Teradata Learning - Triggers (02/17/2013)


·        A trigger is a event driven operation that is caused by modification of one or more columns of a row in a table.

·        Note that the original modification request , the trigger and any other subsequent triggers constitute a single transaction.

·        Triggers can be particularly useful when Referential Integrity (RI) is defined. Triggers can be established to implement either the Cascade or Nullify strategies.

·        Object type 'G' in HELP database output means a trigger.

·        What is a triggering statement:

It’s the users SQL that a row to change in the table and causes the trigger to Execute.
A triggering statement can be : INSERT, UPDATE,DELETE,INSERT/SELECT
A triggering statement cannot be : SELECT. ( A select Query cannot cause a trigger to execute)

·        What is a triggered statement:
It's the SQL that gets executed as a result of triggering statement.
A triggered statement can be : INSERT,DELETE,UPDATE,INSERT/SELECT, ABORT/ROLLBACK or EXEC(for macros)
A triggered statement cannot be : BEGIN/END Transaction, COMMIT,CHECKPOINT,SELECT

ABORT/ROLLBACK can be triggered statement, but COMMIT and CHECKPOINT is not.

·        Create Trigger Syntax:

A trigger can be a Row Trigger or Statement trigger.

Row Trigger:

a.    The row type trigger can fire multiple times for each triggering statement , once for every changed row in the table.
 
b.    When using the Row type trigger , the REFERENCING statement provides name of the before image of the row using via the phrase OLD AS, and after image of the row via the phrase NEW AS.
 
c.     Prior to Teradata V2R4, a row trigger could perform any valid DML statement except a SELECT. 
Since V2R4, it is a more ANSI standard implementation and can only perform an INSERT. 
In V2R6 a trigger may call a stored procedure.
 
d.    Basic format and syntax to create row trigger:

CREATE TRIGGER    <Trigger-name>

  <TRIGGER ACTION>  {BEFORE | AFTER | INSTEAD OF}  --> Trigger action

 [ ORDER <sequence-number> ]  --> Sequence of the trigger

  <TRIGGERING ACTION >  {INSERT | UPDATE | DELETE | INSERT/SELECT}   --> Triggering action

 [ OF (<column-name>, … ) ]   ON   <subject-table>  --> specifies the column of subject table to be monitored

REFERENCING  
      OLD AS <before-imaged-row>    --> before image of the row
      NEW AS <after-image-row>  --> after image of the row

  FOR EACH ROW  --> indicates that it’s a row type trigger

       [ WHEN (optional condition) ]

  ( <TRIGGERED ACTION>  { INSERT | INSERT/SELECT | UPDATE | DELETE }   ; )     --> Triggered action

In the row trigger the triggered action shown is only INSERT and INSERT/SELECT.
Try using DELETE AND UPDATE.


Statement Trigger:

a.    Statement trigger will fire only once for each triggering statement.
 
b.    When  using  the  STATEMENT  type  of  trigger,  the REFERENCING will use OLD_TABLE for all before image rows and NEW_TABLE for all after image rows.

CREATE TRIGGER    <Trigger-name>
  <TRIGGER ACTION>  {BEFORE | AFTER | INSTEAD OF}

 [ ORDER <sequence-number> ]

  <TRIGGERING ACTION > {INSERT | UPDATE | DELETE | INSERT/SELECT}  

 [ OF (<column-name>, … ) ]   ON   <subject-table>

REFERENCING   
      OLD_TABLE AS  <before-image>     --> old table
      NEW_TABLE AS <after-image>  --> New table

  FOR EACH STATEMENT --> Indicates that it’s a statement trigger

       [ WHEN (optional condition) ]

  ( <TRIGGERED ACTION>  { INSERT | INSERT/SELECT | UPDATE | DELETE | 
                                 ABORT/ROLLBACK | EXEC }   ; )    
;

EXEC is for calling a macro.

Following is the meaning of each part of the TRIGGER syntax:

·        BEFORE, AFTER, and INSTEAD OF.:  The implication here regards when the trigger fires. 
It either fires
BEFORE the user request,
AFTER the user request
or
INSTEAD OF the user request.

·        ORDER (optional): Used to sequence the firing of triggers.  This is only a consideration  when  more  than  one  trigger  is  based  on  the  same  trigger  action (BEFORE, AFTER or INSTEAD OF).

·        Triggering Statements:   There are four types of SQL  triggering  statements  that  can  cause  a  trigger  to  fire,  they  are:    INSERT, INSERT/SELECT, UPDATE, and DELETE.

When the triggering statement is an UPDATE, the optional OF portion is probably needed.  It names one or more specific column(s) to monitor for a change in the data.

Note: When using multiple column names in the OF clause, If data in all columns named in the OF, is changed the trigger will fire.  Otherwise, if the data is changed in only one of the columns, the trigger does not fire.  It is all or nothing regarding the columns being monitored for a change. 
When the OF is not used, the value in every column must change in order for the trigger to fire.

·        Subject Table: The table name specified by the ON is referred to as the subject table.
Note :  A trigger can only have one subject table.


·        OLD AS, NEW AS: The OLD and NEW AS references can come together only when the triggering statement is an UPDATE. When triggering statement is UPDATE then only the BEFORE AND AFTER image makes sense.

When the triggering statement is an INSERT, there is only a NEW AS reference name because there is only a new row and not an old row. 

Conversely, when the triggering statement is a DELETE, there is only an OLD AS reference name because the row no longer exists.

·        WHEN clause: The optional WHEN designator provides a last conditional test to make before actually
executing the trigger.  Example: When you create a trigger to perform a specific action. However we may not want trigger to execute during the day time or if the data change is not more than 10% . In such cases we use a WHEN clause which makes one final check before letting the trigger to execute.

WHEN is the place where we can use SELECT in the subquery. Only place in trigger where SELECT is allowed.

·        Triggered action:   It is an SQL triggered statement that will  execute as a result of the triggering statement. 
There are six SQL statements that can be a triggered statement, they are:  INSERT, INSERT/SELECT, UPDATE, DELETE, EXEC and ABORT/ROLLBACK. 

Remember that a trigger is part of a transaction and cannot begin a new transaction. This is the reason why COMMIT is not allowed. The reason is that COMMIT will start a new transaction.


·        BEFORE Trigger:

The BEFORE trigger is executed prior to the maintenance requested by the user‟s client application.  Once the trigger has successfully completed, the actual modification of the row(s) in the subject table is done for the user.

Example of BEFORE trigger to apply RI.


We create two tables as show below:
CREATE MULTISET TABLE MY_TEST_DB.EMP_TABLE, NO FALLBACK
(
emp_no integer,
dept_no integer,
DW_LOAD_DT date
);

CREATE MULTISET TABLE MY_TEST_DB.DEPT_TABLE, NO FALLBACK
(
dept_no integer,
DW_LOAD_DT date
);


Then we executed the below trigger statement to create a trigger to insert a row to dept_table before inserting it to emp_table.

CREATE TRIGGER trigger1
BEFORE INSERT on MY_TEST_DB.EMP_TABLE
REFERENCING
NEW AS newrow  /*since trigger action is INSERT , we can only use NEW AS*/
FOR EACH ROW
(INSERT into  MY_TEST_DB.DEPT_TABLE values (newrow.dept_no,date ););

However this failed with the reason :
CREATE TRIGGER Failed. 5424:  A triggered action statement is the wrong kind. 

On investigating we found below information:

The following are the valid triggered actions for the AFTER triggers:
1. INSERT
2. DELETE
3. UPDATE
4. ABORT
5. EXEC macros containing any of the above statements.

The following are the valid triggered actions for the BEFORE triggers:
1. ABORT
2. EXEC macros containing ABORT statements only.
3. SET (only for BEFORE row triggers)


Thus as explained above we cannot use the triggered statement INSERT when trigger action is BEFORE.

Hence we inserted a row in dept_table with dept number 200.
Now we create a row to abort a transaction of rows are inserted to employee table apart from dept no 200.

CREATE TRIGGER MY_ONE_DAY_TABLES.trigger2
BEFORE INSERT ON MY_TEST_DB.EMP_TABLE
REFERENCING NEW ROW AS newrow
FOR EACH ROW
WHEN ( newrow.dept_no NOT IN (select dept_no from MY_TEST_DB.DEPT_TABLE))
(ABORT;);

Here the trigger is created under MY_ONE_DAY_TABLES database and tables are in MY_TEST_DB database.
Hence there are chances that the insert will fail with below reason:

INSERT Failed. 3523:  An owner referenced by user does not have SELECT WITH GRANT OPTION access to MY_TEST_DB.EMP_TABLE. 

Here owner of the trigger is now MY_ONE_DAY_TABLES. Hence we grant the access using below queries. This may not be required on all systems.

GRANT SELECT,INSERT,UPDATE on MY_TEST_DB.EMP_TABLE TO MY_ONE_DAY_TABLES WITH GRANT OPTION;
GRANT SELECT,INSERT,UPDATE on MY_TEST_DB.DEPT_TABLE TO MY_ONE_DAY_TABLES WITH GRANT OPTION;

Select * from MY_TEST_DB.DEPT_TABLE;
dept_no        DW_LOAD_DT
200                   2/16/2013

insert into MY_TEST_DB.EMP_TABLE VALUES(2000,200,date);  --> This works fine with 1 row inserted.

insert into MY_TEST_DB.EMP_TABLE VALUES(2000,300,date); --> This will not insert any rows and the dept value 300 is not in the dept_table.

Thus now we know how to use BEFORE trigger.


Note: We cannot drop the tables EMP_TABLE and DEPT_TABLE is there is a trigger defined on the table.
Other wise we would get following error message:
Statement 1: DROP TABLE Failed. 5428:  Table 'EMP_TABLE' has defined triggers. 


·        After Trigger:

The AFTER trigger is executed or fired subsequent to the original update requested by the user's client application.  Once the trigger has successfully completed, the user request part of the transaction is finished.

In the below trigger a row will be inserted to DEPT_TABLE for every row being inserted to EMP_TABLE.
CREATE TRIGGER MY_ONE_DAY_TABLES.trigger1
AFTER INSERT on MY_TEST_DB.EMP_TABLE
REFERENCING
NEW AS newrow
FOR EACH ROW
(INSERT into  MY_TEST_DB.DEPT_TABLE values (newrow.dept_no,date ););
/* ; semil colon shoul be at the end of the triggered statement*/

insert into  MY_TEST_DB.EMP_TABLE values (100,200,date);

select * from MY_TEST_DB.DEPT_TABLE;

dept_no        DW_LOAD_DT
200        2/16/2013


Update using trigger

CREATE TRIGGER MY_ONE_DAY_TABLES.trigger1
AFTER INSERT on MY_TEST_DB.EMP_TABLE
REFERENCING
NEW AS newrow
FOR EACH ROW
(UPDATE MY_TEST_DB.DEPT_TABLE SET DW_LOAD_DT=newrow.DW_LOAD_DT - 1 where dept_no=newrow.dept_no ;);

/* ; semil colon should be at the end of the triggered statement*/



insert into  MY_TEST_DB.EMP_TABLE values (1030,200,date);

select * from MY_TEST_DB.DEPT_TABLE

dept_no        DW_LOAD_DT
200                  2/15/2013



Statement Trigger:
CREATE TABLE MY_TEST_DB.EMP_TABLE_2, NO FALLBACK AS MY_TEST_DB.EMP_TABLE WITH NO DATA AND STATS

insert into MY_TEST_DB.EMP_TABLE_2 values (100,1000,date);
insert into MY_TEST_DB.EMP_TABLE_2 values (200,2000,date);
insert into MY_TEST_DB.EMP_TABLE_2 values (300,3000,date);

CREATE TRIGGER MY_ONE_DAY_TABLES.trigger1
AFTER INSERT on MY_TEST_DB.EMP_TABLE
REFERENCING
NEW TABLE AS newtable
FOR EACH STATEMENT
(INSERT INTO MY_TEST_DB.DEPT_TABLE SELECT dept_no,date from newtable;);
/* ; semil colon shoul be at the end of the triggered statement*/

insert into MY_TEST_DB.EMP_TABLE select * from MY_TEST_DB.EMP_TABLE_2;

select * from MY_TEST_DB.DEPT_TABLE

dept_no        DW_LOAD_DT
1        2000        2/16/2013
2        1000        2/16/2013
3        3000        2/16/2013



·        INSTEAD OF trigger:

The INSTEAD OF trigger provides the ability to execute the trigger and not do the user‟s  client  application  request  at  all.

This is no longer valid as of V2R5.0.

The following INSTEAD OF trigger is created on the parts table to insert an audit row
into the delay table indicating that an UPDATE on the parts table should have taken
place.  However, because it was going to impact more than 10 rows in the subject table, it
was not performed:


CREATE TRIGGER    DelayUpd
INSTEAD OF UPDATE OF (price) ON parts
  REFERENCING   OLD_TABLE  AS oldtab            
    NEW_TABLE AS newtab
    FOR EACH STATEMENT
WHEN ( 10 < (SELECT COUNT(part_num) FROM oldtab  WHERE part_num = oldtab.part_num)      )
 (  INSERT INTO Delay_Tab 
SELECT DATE, TIME, 'Upd', newtab.part_num, newtab.price
FROM newtab WHERE newtab.part_num = part_num ; ) ;

Note the way we find out the count of rows that are gonna get updated and then decide what should be done.


·        Cascading Triggers:

A  cascading  trigger  results  when  a  triggering  statement fires a trigger, which in turn fires another trigger.

The only thing a trigger cannot do is to change the subject table  on  which  the  trigger  is  defined.  However,  a subsequent trigger may come back and update the original subject table.  Caution should be exercised here so that the triggers do not cascade indefinitely.  This constitutes an infinite loop and will cascade until they run out of either Permanent or Transient Journal space, or the transaction is aborted.



CREATE table MY_TEST_DB.REV_ADJ_CODEYELLOW, NO FALLBACK
(
amount decimal (10,2),
datets timestamp
);

CREATE table MY_TEST_DB.REV_ADJ_CODEORANGE, NO FALLBACK
(
amount decimal (10,2),
datets timestamp
);


CREATE table MY_TEST_DB.REV_ADJ_CODERED, NO FALLBACK
(
amount decimal (10,2),
datets timestamp
);

Initially the adjustment amount is inserted into the CODEYELLOW Table.
If this amount is more than Rs. 1000 then the trigger 'trigger1' gets executed and inserts row to CODEORANGE. If the amount is larger than 10000 the another trigger 'trigger2' is executed which inserts rows in CODERED.

CREATE TRIGGER MY_ONE_DAY_TABLES.Trigger1
AFTER INSERT ON MY_TEST_DB.REV_ADJ_CODEYELLOW
REFERENCING NEW AS new_row
FOR EACH ROW
WHEN ( amount > 1000)
(INSERT INTO MY_TEST_DB.REV_ADJ_CODEORANGE values (new_row.amount,current_timestamp););


CREATE TRIGGER MY_ONE_DAY_TABLES.Trigger2
AFTER INSERT ON MY_TEST_DB.REV_ADJ_CODEORANGE
REFERENCING NEW AS new_row
FOR EACH ROW
WHEN ( amount > 10000)
(INSERT INTO MY_TEST_DB.REV_ADJ_CODERED values (new_row.amount,current_timestamp););


GRANT SELECT,INSERT,UPDATE ON MY_TEST_DB.REV_ADJ_CODERED TO MY_ONE_DAY_TABLES WITH GRANT OPTION;
GRANT SELECT,INSERT,UPDATE ON MY_TEST_DB.REV_ADJ_CODEORANGE TO MY_ONE_DAY_TABLES WITH GRANT OPTION;
GRANT SELECT,INSERT,UPDATE ON MY_TEST_DB.REV_ADJ_CODEYELLOW TO MY_ONE_DAY_TABLES WITH GRANT OPTION;

Then we perform inserts as below:

insert into MY_TEST_DB.REV_ADJ_CODEYELLOW values (123.12, CURRENT_TIMESTAMP);
insert into MY_TEST_DB.REV_ADJ_CODEYELLOW values (1234.12, CURRENT_TIMESTAMP);
insert into MY_TEST_DB.REV_ADJ_CODEYELLOW values (153.12, CURRENT_TIMESTAMP);
insert into MY_TEST_DB.REV_ADJ_CODEYELLOW values (1213.12, CURRENT_TIMESTAMP);
insert into MY_TEST_DB.REV_ADJ_CODEYELLOW values (121312.12, CURRENT_TIMESTAMP);


And then check all the tables:

SELECT * from MY_TEST_DB.REV_ADJ_CODEYELLOW;
amount                   datets
153.12          2/17/2013 00:49:52.720000
121312.12        2/17/2013 00:49:54.290000
1234.12        2/17/2013 00:49:51.920000
123.12                  2/17/2013 00:49:50.950000
1213.12        2/17/2013 00:49:53.480000



SELECT * from MY_TEST_DB.REV_ADJ_CODEORANGE;
121312.12        2/17/2013 00:49:54.290000
1234.12        2/17/2013 00:49:51.920000
1213.12        2/17/2013 00:49:53.480000


SELECT * from MY_TEST_DB.REV_ADJ_CODERED;
Amount         datets
121312.12        2/17/2013 00:49:54.380000



·        Sequencing Triggers:

The ORDER option allows for the definition of a sequence number to be associated with each trigger.  Valid values are 1 through 32,767. 
All triggers with an ORDER of 1, fire before the first ORDER 2 trigger.  All the ORDER 2 triggers fire before the first 3 and so on until there are no more triggers associated with the initial triggering statement.

When ORDER is not specified, it defaults to a value of 32,767.  Triggers with the same ORDER
value fire randomly until the last one with that same value finishes.

Example:

As an example, instead of using the cascading triggers seen previously, it might be
desirable to have both triggers defined on CODEYELLOW


CREATE TRIGGER MY_ONE_DAY_TABLES.Trigger1
AFTER INSERT ON MY_TEST_DB.REV_ADJ_CODEYELLOW
ORDER 100
REFERENCING NEW AS new_row
FOR EACH ROW
WHEN ( amount > 1000)
(INSERT INTO MY_TEST_DB.REV_ADJ_CODEORANGE values (new_row.amount,current_timestamp););


CREATE TRIGGER MY_ONE_DAY_TABLES.Trigger2
AFTER INSERT ON MY_TEST_DB.REV_ADJ_CODEYELLOW
ORDER 200
REFERENCING NEW AS new_row
FOR EACH ROW
WHEN ( amount > 10000)
(INSERT INTO MY_TEST_DB.REV_ADJ_CODERED values (new_row.amount,current_timestamp););

Here two triggers with same trigger action on same table are defined.
However we have assigned ORDER 100 to trigger1 and order 200 to trigger2.
This will cause trigger1 to execute first and then trigger2.


No comments:

Post a comment