SQL for QA: Tutorial#14 – Triggers
Triggers are nothing but action / set of actions performed on Tables or Views when Insert / Delete / Update or Truncate queries are executed.
What kind of Actions , Table or View Name and on which events ( for eg Insert / Delete /Update / Truncate ) on which trigger should be executed are specified
Being a Tester or QA , we don’t really have to create a Trigger nor we have the rights to delete it. But usually we get triggers to test if they are working fine and so the set business rules are correct.
To test Trigger, we need to execute the SQL query embedded in the trigger independently first and record the result. Then execute the trigger as whole and Compare the results.
Triggers are useful for enforcing business rules, validating input data, and keeping an audit trail etc.
There are 3 types of triggers
- BEFORE TRIGGER : fired before an event
- AFTER TRIGGER : fired after an event
- INSTEAD OF TRIGGERS: used to bypass an event
Triggers are classified as
- Row level trigger: this kind of trigger fire for each affected row. Example – if we are updating table, this type of trigger fired for each updated row.
Triggers which fire for UPDATE, DELETE, INSERT operation may defined based on row level trigger.
For this type a trigger should be marked FOR EACH ROW
- Statement level trigger: this kind of trigger fired only once after a statement has been executed regardless of how many rows it modifies, it get fired even if the statement did not affect any row.
Triggers attached to TRUNCATE or views fire at the statement level.
For this type a trigger should be marked FOR EACH STATEMENT
In PL/pgSQL a trigger procedure is created with the CREATE FUNCTION command, it is declared like a function with no arguments and a return type should be trigger.
Syntax:
|
1 2 3 4 5 |
CREATE TRIGGER Trigger_Name { BEFORE | AFTER | INSTEAD OF } { UPDATE | INSERT | DELETE | TRUNCATE } ON Table_Name [FOR [EACH] { ROW | STATEMENT } ] EXECUTE PROCEDURE <Function_Name(arguments)> |
1. Trigger_Name: Name of the trigger
2. Table_Name: Name of the table, to which trigger is attached
3. Function_Name: Name of the Stored Function.
Example:
Let us consider following example which demonstrates triggers.
We have 2 database tables 1. Emp_Record 2. Trigger_check
Whenever new record get inserted in Emp_Record table, at the same time one record will get inserted into Trigger_Check table with emp id and timestamp value, this record insertion is nothing but the result of trigger.
Create Trigger:
So the trigger gets fired on Insert event. Let’s create above 2 tables.
Table# 1 : Emp_record
|
1 2 3 |
CREATE TABLE Emp_Record(Eid INT PRIMARY KEY, Ename VARCHAR(20) NOT NULL, Esal INT NOT NULL); |
Table# 2 : Trigger_check
|
1 2 |
CREATE TABLE Trigger_Check (Id INT NOT NULL, Record_Inserted_Time TEXT NOT NULL); |
Function which returns a trigger.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE OR REPLACE FUNCTION Test_tr () RETURNS trigger AS $$ BEGIN INSERT INTO Trigger_Check (Id, Record_Inserted_Time) VALUES (NEW.Eid, Current_Timestamp); RETURN NEW; END; $$ LANGUAGE 'plpgsql'; |
In the above trigger function we are using a ‘NEW‘ which is a PostgreSQL extension to triggers. There are two PostgreSQL extensions to trigger ‘OLD‘ and ‘NEW‘.
OLD keyword is used to refer to the columns of a row before it is affected by a trigger (it is read only) and NEW keyword is used to refer to the columns of a row after it is affected by a trigger.
For INSERT event, only NEW keyword can be used.
While configuring UPDATE trigger both keywords can be used to show the data before updation(OLD) and after updation(NEW).
For DELETE trigger only OLD keyword is used, because there is no new row.
So now, let us create a trigger on Emp_Record table :
|
1 2 3 4 |
CREATE TRIGGER Test_Trigger AFTER INSERT ON Emp_Record FOR EACH ROW EXECUTE PROCEDURE Test_tr(); |
Here we have created trigger Test_Trigger attached to Emp_Record table, and the trigger gets fired after an Insert event. And it is fired for each newly inserted row.
Let’s insert some data in Emp_Record table:
|
1 2 3 |
INSERT INTO Emp_Record VALUES (1, 'Anita', 20000 ); INSERT INTO Emp_Record VALUES (2, 'Janhavi', 42000 ); |
After this insertion trigger get fired and 2 record will get inserted into Trigger_Check table
Above table is the result of a trigger which was fired on INSERT event.
In the same way we can create a trigger on UPDATE and DELETE event.
Drop Trigger :
Syntax to Drop Trigger:
|
1 |
DROP TRIGGER Trigger_Name on Table_Name; |
Example:
|
1 |
DROP TRIGGER Test_Trigger on Emp_Record; |
CONCLUSION:
This is all about trigger, but few points should be noted that triggers is an overhead on system so it makes system run slower because triggers get fired on updation of any field( as per the trigger definition).
In addition to this trigger are very hard to maintain especially Cascading triggers, it needs lots of knowledge and expertise to maintain it.
We can create only one trigger for each event means we can’t write 2 BEFORE UPDATE triggers on same table.
