·
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