|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-ACTIONWe 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.
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.
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.
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.
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.
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.
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