Data Manipulation Language (DML) Triggers.

As the name suggests these are the triggers which execute on DML events or say depend on DML statements such as Update, Insert or Delete. Using DML trigger you can control the behavior of your DML statements.

Since the theory has already been discussed in the previous tutorial hence I won’t bore you further. You can refer to the previous tutorial “Introduction to Triggers” anytime. 

Examples

In order to demonstrate the creation process of DML trigger we need to first create a table.

 CREATE TABLE superheroes (
  sh_name VARCHAR2 (15)
);    

I have created this table with the name SUPERHEROES which has only one column sh_name with data type varchar2 and data width 15.  Now I will write a DML trigger which will work on this table.

So the table is created. Now let’s do some examples which will help you in understanding the concepts more clearly.

Before that a simple tip:
Always remember to set your server output ON otherwise the output message returned from your trigger will not be displayed back to you.

SET SERVEROUTPUT ON;

Example 1. Before Insert Trigger

In the first example we will see how to create a trigger over Insert DML. This trigger will print a user defined message every time a user inserts a new row in the superheroes table.

CREATE OR REPLACE TRIGGER bi_Superheroes
BEFORE INSERT ON superheroes
FOR EACH ROW
ENABLE
DECLARE
  v_user  VARCHAR2 (15);
BEGIN
 SELECT user INTO v_user FROM dual;
 DBMS_OUTPUT.PUT_LINE('You Just Inserted a Row Mr.'|| v_user); 
END;
/

On successfully compiling, this trigger will show you a string along with the user name who performed the “Insert” DML on superheroes table. Thus you check this trigger by Inserting a row in Superheroes table.

INSERT INTO superheroes VALUES ('Ironman');

Example 2: Before Update Trigger.

Update Trigger is the one which will execute either before or after Update DML. The creation process of an Update trigger is the same as that of Insert Trigger. You just have to replace Keyword INSERT with UPDATE in the 2^nd Line of the above example.

CREATE OR REPLACE TRIGGER bu_Superheroes
BEFORE UPDATE ON superheroes
FOR EACH ROW
ENABLE
DECLARE
  v_user  VARCHAR2 (15);
BEGIN
 SELECT user INTO v_user FROM dual;
 DBMS_OUTPUT.PUT_LINE('You Just Updated a Row Mr.'|| v_user); 
END;
/

On successfully compiling, this trigger will print a user defined string with the username of the user who updated the row. You can check this trigger by writing an update DML on the superheroes table.

UPDATE superheroes SET SH_NAME = ‘Superman’ WHERE SH_NAME='Ironman';

Example 3: Before Delete Trigger

Similar to Insert and Update DML you can write a trigger over Delete DML. This trigger will execute either before or after a user deletes a row from the underlying table.

CREATE OR REPLACE TRIGGER bu_Superheroes
BEFORE DELETE ON superheroes
FOR EACH ROW
ENABLE
DECLARE
  v_user  VARCHAR2 (15);
BEGIN
 SELECT user INTO v_user FROM dual;
 DBMS_OUTPUT.PUT_LINE('You Just Deleted a Row Mr.'|| v_user); 
END;
/

You can check the working of this trigger by executing a DELETE DML on underlying table which is superheroes.

DELETE FROM superheroes WHERE sh_name = 'Superman';

Above three examples showed you 3 different triggers for 3 different DML events on one table. Don’t you think that if we can cover all these 3 events in just 1 trigger then it will be a great relief? If you think so then my dear friend I have some good news for you. Let me show you how we can achieve this feat.

INSERT, UPDATE, DELETE All in One DML Trigger Using IF-THEN-ELSIF

CREATE OR REPLACE TRIGGER tr_superheroes
BEFORE INSERT OR DELETE OR UPDATE ON superheroes
FOR EACH ROW
ENABLE
DECLARE
  v_user  VARCHAR2(15);
BEGIN

  SELECT 
    user INTO v_user FROM dual;
  IF INSERTING THEN
    DBMS_OUTPUT.PUT_LINE('one line inserted by '||v_user);
  ELSIF DELETING THEN
    DBMS_OUTPUT.PUT_LINE('one line Deleted by '||v_user);
  ELSIF UPDATING THEN
    DBMS_OUTPUT.PUT_LINE('one line Updated by '||v_user);
  END IF;
END;
/

Using this one trigger you can achieve the same results as that of the above three triggers. I have explained every single line of this trigger along with the other three triggers in detail in my Video tutorial. I highly suggest you to watch that tutorial.

Hope you enjoyed reading and learnt something. I request you to share this article with your friends via Twitter, Facebook or any means you prefer. Thanks & have a great day!