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

  1. BEFORE TRIGGER : fired before an event
  2. AFTER TRIGGER : fired after an event
  3. INSTEAD OF TRIGGERS: used to bypass an event

Triggers are classified as

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

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

Table# 2 : Trigger_check

Function which returns a trigger.

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 :

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:

After this insertion trigger get fired and 2 record will get inserted into Trigger_Check table

Trigger

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:

Example:

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.

Add a Comment

Your email address will not be published. Required fields are marked *