Triggers

Teradata Triggers:

Ø A trigger is an object in a database, like a macro or view.

Ø A trigger is created using CREATE TRIGGER statement.

Ø A trigger actually defines a set of one or more SQl statements associated with the table and is fired when the triggering event occuers.

Ø Just like any other database objects following statements can be run along with the TRIGGERS:

1.        CREATE TRIGGER

2.      DROP TRIGGER

3.      SHOW TRIGGER

4.      ALTER TRIGGER

5.      RENAME TRIGGER

6.      REPLACE TRIGGER

7.       HELP TRIGGER

With what are Triggers non compatible?

Triggers cannot be used with :

1)       Fastload and Multiload

2)     Updatable cursors(stored procedures and pre-processor)à To use Fastload, multioad and updateable cursors with tables which have trigger defined on them , we first have to disable the trigger. Triggers can be disabled with the help of ALTER TRIGGER statement. It has a option of enabling or disabling the cursor.

3)     Join indexesà Join indexes are never permitted on tables which have defined triggers.

When does trigger fire?

A trigger fires when the triggering event occurs. A triggering statement is an SQL statement which causes a trigger to fire. It is the 'launching' statement. When trigger fires, it causes triggered events to occur.

Which SQl statements cause trigger to fire?

Triggering statements is SQl that causes a trigger to fire include:

1.        Inserts

2.      Updates

3.      Delete

4.      Insert-Select

(note that a SELECT does not act as launching event. Doing a select on a table does not lead trigger to fire)

Which statements can be triggered as response to the triggering statement?

Triggered statements can be :

1.        INSERT

2.      UPDATE

3.      DELETE

4.      INSERT SELECT

5.      ABORT/ROLLBACK

6.      EXEC (macro)

Note that this also does not include SELECT.

Though rollback and abort is included, COMMIT and CHECKPOINT are not permitted.

Which statements can never be used as triggered statements?

Triggered statements may never be any of these:

1.        BEGIN TRANSACTION

2.      CHECKPOINT

3.      COMMIT

4.      END TRANSACTION

5.      SELECT

Though BT and ET are not allowed , there are ways to create transactions.

The triggering statement and triggered statements are considered as a single transaction by Teradata. Internally TD creates a macro and submits it.

CREATE TRIGGER syntax and components of the syntax:

A  Trigger contains several components and they need to be specified in correct syntax.

Following are the components in the order in which they should be specified.

1)       Trigger Name. (Name given to the trigger object)

2)     Trigger Time (we should specify when the triggered statements should run respect to the triggering event...AFTER or BEFORE the triggering event. We can cause the triggered event to run before the triggering event)

3)     Trigger event: Indicates the DML statement that causes the trigger to work. It could be INSERT, UPDATE, and DELETE etc.

4)     Column of the Subject table: Trigger may run when trigger event is performed on all or some of the columns of the subject table. This is especially used with UPDATE trigger event to monitor only specific columns for change.

5)     Subject table: Subject table is the table with which trigger is associated. This means the table on which the triggering action is performed.

6)     REFERENCING clause: Defines how old and new values for the table changes will be named . Detail rules are explained later.

7)      Trigger type: Trigger could be of ROW or Statement Type. Statement triggers once for one statement . Row type triggers for each of the row affected in the subject table.

8)     Trigger Action: The combination of the Triggered Statements and the WHEN clause. This should be mentioned inside brackets.

Example (INSERT trigger event):

CREATE MULTISET TABLE EDW_RESTORE_TABLES.EMPLOYEE

( Employeeid INTEGER,

Employeename CHAR(30),

Salary INTEGER)

CREATE MULTISET TABLE EDW_RESTORE_TABLES.EMPLOYEE_LOG

(

Employeeid INTEGER,

Employeename CHAR(30),

Salary INTEGER,

userid CHAR(30) DEFAULT USER,

loaddt DATE DEFAULT DATE,

loadtm  INTEGER DEFAULT TIME)

CREATE TRIGGER employeetrr

AFTER INSERT ON EDW_RESTORE_TABLES.EMPLOYEE

REFERENCING NEW_TABLE AS employeenew

/*With insert trigger event we cannot use OLD_TABLE AS clause*/

FOR EACH STATEMENT

( INSERT INTO EDW_RESTORE_TABLES.EMPLOYEE_LOG(Employeeid,Employeename,Salary) SELECT Employeeid,Employeename,Salary FROM employeenew;)

/* the inner SQl should have its own semicolon as the end of the SQL*/

INSERT INTO EDW_RESTORE_TABLES.EMPLOYEE(Employeeid,Employeename,Salary)  VALUES (12345,'SUKUL MAHADIK',32000);

SELECT * FROM EDW_RESTORE_TABLES.EMPLOYEE_LOG;

Employeeid       Employeename              Salary              userid               loaddt               loadtm

12345              SUKUL MAHADIK         32000 SM017R           02/15/2012    224943

Thus we can see that insert into the EMPLOYEE_TABLE triggers an event that creates a record in EMPLOYEE_LOG table.

Syntax in detail (UPDATE trigger event):

CREATE TRIGGER MgrUpdate

AFTER UPDATE OF (managername) ON

REFERENCING OLD_TABLE as olddepartment

                        NEW_TABLE as department

FOR EACH STATEMENT

WHEN (optional condition)

(UPDATE employee SET employee_manager_name = newdepartment.managername

WHERE employee_manager_name = c.managername;

)

IN the above example:

MgrUpdate à is the name of the trigger.

AFTER à indicates the trigger time. Ie AFTER or BEFORE the triggering events.

UPDATE à indicates the trigger event. This indicates what operation(IsnERT,UPDATE,DELETE) should  trigger the event

OF(managername) à indicates which column should be monitored for update. If we omit this it will trigger for update on any of the columns of the table.

Departmentà indicates the subject table which is the table on which trigger is defined.

REFERENCING OLD_TABLE as olddepartment,NEW_TABLE as newdepartmentà indicates how the new and old table should be  referred. After the update the table with get changed. So if we need to access the value that were prior to update we need to create a reference to the old table. This can be done using the REFERENCING clause. With UPDATE type of trigger event we can use both (OLD and NEW).

FOR EACH STATEMENTà indicates the type of trigger. Row or statement.

WHEN à when is optional and can be used to additional conditions on when the triggered action should take place.

Inside the brackets is the triggered event and must have its own semicolon.

Trigger options in detail:

1)       Trigger Types:

Triggers are of two types:

·       Row Triggers

·       Statement Triggers

Row Triggers:

This is fired once for every row affected in the triggering statement.

OLD and NEW are used to refer to the rows of the subject tables.(not OLD_TABLE and NEW_TABLE)

Example: If an update operation affects 3 different rows in the subject table then the triggered operation will be performed thrice.

Statement Triggers:

This is fired once per statement.

This means that for one update/insert/delete operation this will be performed once irrespective of how many rows are affected.Statement Triggers fire once for each statement executed.

Statement triggers use an alias to define the old and new tables rather than individual rows.

            OLD_TABLE and NEW_TABLE claues are used to refer the old and new subject table.

Statement triggers work on sets of rows just like regular SQL statements.

2)     Triggering action times:

Ø BEFORE - the Triggered Statements will execute before the Triggering Statement.

Ø AFTER - the Triggered Statements will execute after the Triggering Statement.

Ø INSTEAD OF - the Triggered Statements will execute instead of the Triggering Statement.

In Teradata V2R5.1, INSTEAD OF triggers are no longer supported, consistent with the ANSI standard.

3)     UPDATE OF

When trigger is created for update event type then we can specify the columns to be monitored for change. This can be done by using “UPDATE OF “in the create trigger query:

Example: 

...BEFORE UPDATE OF (empno, deptno) ON employee...

Trigger is triggered if either of the columns empno or deptno or both are updated.

...AFTER UPDATE ON employee...

Above triggers the trigger if any of the columns of the employee table changes.

4)    WHEN clause:

Ø When we use “FOR EACH ROW” the trigger is fired for each of the affected row. However we might want trigger to fire only for rows that satisfy certain conditions. These conditions may be specified using WHEN clause. 

Example:

In employee table all employees would be updated to increase the salary. Thus all rows will be affected. However we may want the trigger to fire only when percentage of increase in the salary is greater than 10%.

Thus a WHEN clause specifies which conditions must be met in order for the trigger to fire

Ø A limitation of the WHEN clause is that it may only reference rows of the subject table which are involved in the triggering action. This means that if an update affects only 5 rows in a table of 1000 rows then only those five rows may be seen for purposes of WHEN clause conditions.

Ø Result of the when clause is true, False of Null. Null is treated a False. When False the trigger wont be fired.

Ø Example of using when:

CREATE TRIGGER raiseTrig

AFTER UPDATE OF (salary_amount) ON employee ORDER 1

        REFERENCING OLD AS oldrow

                    NEW AS newrow

        FOR EACH ROW

WHEN((newrow.salary_amount-oldrow.salary_amount)/

                                    oldrow.salary_amount>.10)

(INSERT INTO salary_log values

(newrow.last_name, oldrow.salary_amount

,newrow.salary_amount,date););

Ø IN the above query though we have specified “FOR EACH ROW”, the trigger won’t fire for each of the affected row. Instead the when condition will be analysed for each of the affected row(note that only affected rows are visible to WHEN clause) and only if the salary hike is greater than 10 percent then only the trigger will fire.

Thus when acts as a WHERE clause to determine when the clause should apart from the triggering event. Thus Its additional condition that should be satisfied for triggering condition to be satisfied for trigger to fire.

Ø With WHEN we can perform aggregate functions on the rows that are involved in the triggering action. However this has to be a part of subquery.

CREATE TRIGGER StageUpd

     INSTEAD OF UPDATE OF (salary_amount) ON employee

REFERENCING OLD_TABLE AS oldtable

          NEW_TABLE AS newtable

     FOR EACH STATEMENT

WHEN (TIME BETWEEN 80000 AND 170000

AND 6<(SELECT COUNT(employee_number) FROM oldtable)

     )

(INSERT INTO StageTbl

SELECT DATE, TIME, 'Update',

employee_number, salary_amount FROM newtable;);

Note the use of the count query. COUNT is legal in this context because it is part of a subquery

Also, the COUNT function will only count the rows of of oldtable which are seen by the trigger. In this case, only the employee rows being updated are seen, thus the count will reflect that number, not the count of all employees.

Also note that when “WHEN” is used we used “FOR EACH ROW”

WHEN Limitations:

Aggregates are permitted in WHEN clause if in a subquery.

Row level SELECTs are permitted in the WHEN clause only with statement triggers.

Moral is “The WHEN clause basically tests to see if you need to fire the trigger. Only the rows that are affected by the update statement can be referenced by the WHEN clause.”

5)     ORDER clause:

Ø ORDER clause is used to determine the sequencing of triggers if multiple triggers are defined on the same table.

Ø  ORDERs clause is essential when two or more triggers defined on the table have 3 common attributes:

·       The same Trigger Action Time - (BEFORE, AFTER, INSTEAD OF)

·       The same Trigger Event - (UPDATE, DELETE, INSERT)

·       The same Trigger Type - (ROW or STATEMENT)

Ø If multiple triggers specified on the table have all the 3 attributes same and ORDER is not specified then random sequencing is used for the triggered statements.

Ø Triggers meeting the above criteria and also having the same ORDER value, will fire in a random sequence.

Ø An ORDER value may be set from 1 to 2,147,483,647 with 32,767 being the default.

Ø Example:

CREATE TRIGGER Trigger_4                 :

AFTER UPDATE OF (col_1) ON Tbl_1  ORDER 1

...  FOR EACH STATEMENT

(DELETE FROM Tbl_2 WHERE...........;);

CREATE TRIGGER Trigger_5                 :

AFTER UPDATE OF (col_1) ON Tbl_1  ORDER 2

...  FOR EACH STATEMENT

(INSERT INTO SELECT ... WHERE...............;);

As we can see two triggers have exactly same attributes... Same Trigger  time(AFTER0, Same event type(UPDATE) and same trigger type(STATEMENT).

In such cases the ORDER (not ORDER BY) determines which Trigger should run first.

Also note that ORDER is written after the subject table name.

6)     Conditional Triggers Using WHERE

We can also use WHERE clause to conditionally fire the Trigger, just as we do with WHEN clause.

Where Can reference only those rows of subject table qualified by the triggering statement.

Example:

CREATE  TRIGGER raiseTrig

AFTER UPDATE OF (salary_amount) ON employee ORDER 1

            REFERENCING OLD_TABLE AS oldtable

            NEW_TABLE AS newtable

            FOR EACH STATEMENT

(INSERT INTO salary_log

 SELECT n.last_name

            , o.salary_amount

            , n.salary_amount

FROM newtable n

  ,oldtable o

WHERE n.last_name=o.last_name

AND (n.salary_amount - o.salary_amount) /

     o.salary_amount > .10;);

Note that here we use “FOR EACH STATEMENT” and in the query to be triggered we refer the subject table.

Cascading triggers:

Ø Cascading Triggers are triggers whose triggered statements cause other triggers to fire.

Ø There is no limit to how many levels of cascading a triggering statement may cause. Can cascade indefinitely till system resources are exhausted.

Ø It is not possible for a trigger to modify its subject table. (This could cause an infinite loop if the triggering statement caused the trigger to fire again.)

(Note: In Teradata V2R5.1, recursive triggers are supported, meaning a triggered action can modify the subject table, possibly causing the trigger to fire again. Recursive triggers are limited to 16 levels of recursion.)

Referencing rules:

Ø REFERENCING rules and syntax are slightly different for Row and Statement triggers.

1.        Row triggers use the alias for old and new versions of the row being affected by the triggering action.

2.      Statement triggers use a similar alias for the old and new versions of the table affected by the triggering statement.

Ø Following important rules must be followed while using Referencing

1.        OLD and NEW always reference rows. These are called 'correlations'.

2.      OLD_TABLE and NEW_TABLE reference tables. These are called 'aliases'.

3.      At most, one reference each to old and new may be used in a trigger.

4.      No mixing of row and table referencing is permitted. We cannot have old reference as a row reference(OLD) and new reference as table reference(NEW_TABLE)

5.      INSERTS never need an old reference. If we put OLD reference(OLD or OLD_TABLE , it gives an error)

6.      DELETES never use a new reference.

7.       Both the WHEN clause and the triggered statement may reference the subject table via the referencing clause.  This is referred to as an ‘outer reference’.

    Enabling and disabling triggers:

As mentioned earlier the Triggers cannot work with MLoad, FAstload. Hence during such operations we need to remove any triggers. However it’s not good to always drop all the triggers and then recreate it. Hence we have an option of disabling and enabling triggers.

Triggers may be disabled when they are not desired and re-enabled when needed again

The syntax to enable or disable a trigger is:

ALTER TRIGGER [triggername] [ENABLED or DISABLED];

HELP on Triggers:

 Performing a help on trigger provides important information about the trigger.

 Following is what a HELP TRIGGER statement gives:

Name                                                StageUpd
ActionTime                                     I
Event                                                 U(Update)
Kind                                                   S(Statement)
Decimal Order Value                    31767(order value)
Enabled                                           N(not enables)
Comment   ?(we can add comments using COMMENT ON statement

Transcactions and Triggers:

All statements associated with a trigger are treated as a single transaction. This includes both the triggering and triggered statements. Internally, Teradata treats all the statements as a multi-statement macro. Failure of any individual statement causes the entire transaction (or macro) to be rolled back. This is done independently of whether ANSI or Teradata mode is specified for the session.

Example:

CREATE TRIGGER trig1 AFTER INSERT ON tab1

REFERENCING NEW_TABLE AS newtable

FOR EACH STATEMENT

(INSERT INTO Tab2 SELECT a+10, b+10, c FROM newtable;

 UPDATE Tab3 SET h = newtable.b WHERE g = newtable.a;);

In the above both the statements are considered as a single transaction.
Also note that each query has its own semicolon

Optionally, the BEGIN ATOMIC and END statements may be used to bracket the triggered statements, however they are not necessary

            CREATE TRIGGER trig1 AFTER INSERT ON tab1

REFERENCING NEW_TABLE AS newtable

FOR EACH STATEMENT

BEGIN ATOMIC

(INSERT INTO Tab2 SELECT a+10, b+10, c FROM newtable;

 UPDATE Tab3 SET h = newtable.b WHERE g = newtable.a;)

END;

REFERENTIAL INTEGRITY CHECKS:

Ø Referential Integrity (RI) checks are made following each triggered statement in a triggered action.

Ø Triggers, unlike macros, do not have an EXEC-like privilege. With EXEC you need not have access on the underlying tables. But this is not the case with triggers. You need to have privileges on the objects used in the triggered statements for trigger to execute.

Ø CREATE TRIGGER text is limited to 12,500 bytes.