Syntax for Create Trigger in IBM i AS400

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

Read also :

• SQL Triggers concept in IBM i DB2
• Simple SQL Trigger Example
• Conditional SQL Trigger Example
• OF Clause in SQL Trigger
• Multiple Event Execution Through SQL Trigger
• Change Row Before Inserting In Table (Before SQL Trigger)
• Stored Procedure calling in SQL Trigger
• Transition Tables in SQL Triggers
• Error Handling in SQL Trigger Using Signalling
• Self Referencing SQL trigger
• INSTEAD OF SQL Trigger (Adding / Deleting / Inserting record in the table through SQL View)
• Trigger Limitations and Program Attributes
• Effects On Trigger When New File Field Is Added
• SYSTRIGGERS Catalog Table for SQL Trigger
• SYSTRIGDEP Catalog Table for SQL Trigger
• SYSTRIGUPD Catalog Table for SQL Trigger
• SYSTRIGCOL Catalog Table for SQL Trigger