Syntax for Create Trigger in IBM i AS400

Syntax for Create Trigger in IBM i AS400
Syntax for Create Trigger in IBM i AS400, CREATE TRIGGER, sql trigger, trigger, as400, ibmi, db2, db2 for i sql, sql sql programming, iseries, db2
The syntax for Create Trigger in IBM i AS400

This article will have the syntax for creating SQL triggers using CREATE TRIGGER statement.

Syntax For CREATE TRIGGER

CREATE TRIGGER TRIGGER NAME
TRIGGER-ACTIVATION-TIME
TRIGGER-EVENT ON TABLE OR VIEW NAME
TRANSITION-VARIABLES / TRANSITION-TABLE
TRIGGER-GRANULARITY
TRIGGER-MODE
TRIGGERED-ACTION
We can write the same syntax also as shown below
CREATE OR REPLACE TRIGGER TRIGGER NAME
TRIGGER-ACTIVATION-TIME
TRIGGER-EVENT ON TABLE OR VIEW NAME
TRANSITION-VARIABLES / TRANSITION-TABLE
TRIGGER-GRANULARITY
TRIGGER-MODE
TRIGGERED-ACTION

CREATE TRIGGER / CREATE OR REPLACE TRIGGER 

This statement will accept the name of the trigger and create the trigger with the same name in the schema. Also, Create Or Replace trigger can be used when an existing trigger with the same name needs to be replaced and a new trigger with the same name needs to be created.

TRIGGER-ACTIVATION-TIME

Using this statement activation time of the trigger execution is set like BEFORE, AFTER, or INSTEAD OF.
The trigger is executed before the change of the table, after the change of the table, or instead of any of the data manipulation options.

TRIGGER-EVENT ON 

This statement accepts the Table or View name on which trigger has to be present and further an event such as ADD, DELETE or INSERT should be attached on which trigger will be executed. Multiple trigger events can be added to the single table or view.

TRANSITION-VARIABLES  / TRANSITION-TABLE 

This statement gives the column value for the current row that is involved in the trigger event. It's an optional statement while creating the trigger.

TRIGGER-GRANULARITY

This statement is for the execution of the trigger on the basis of FOR EACH STATEMENT or FOR EACH ROW.
When multiple rows are getting altered in a table by a single statement then it's very important to declare that the trigger in the table should be executed for all the rows altered or it should be executed on the basis of the statement.

TRIGGER-MODE

This statement is to decide when the trigger will get executed. After each row alteration MODE DB2ROW  or after the complete alteration of rows in the table MODE DB2SQL.

TRIGGERED-ACTION

In this statement actions that need to be taken trigger is triggered have to be mentioned under BEGIN and END block. Conditional options can also be used in the trigger action.

Related Post

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain