PL/SQL DML Trigger With Before Insert, Update and Delete DML Examples

To test this trigger, first of all we have to create a table.

Table:

create table heros

(hero_name varchar2(30));

Result:

Table HEROS created.

Now we create a dml trigger,

CREATE OR REPLACE TRIGGER bi_heros

BEFORE INSERT ON heros

FOR EACH ROW 

ENABLE

DECLARE

v_user varchar2(30);

begin

select user into v_user from dual;

dbms_output.put_line('User '||v_user||' Just inserted a line');

end;

/

Result:

Trigger BI_HEROS compiled

To check that is this trigger working or not, We Insert a row into the table and find out the result.

insert into heros values('Talha Zubair');

Result:

1 row inserted.

User HR Just inserted a line

We add another trigger on heros table.

CREATE OR REPLACE TRIGGER bu_heros

BEFORE update ON heros

FOR EACH ROW 

ENABLE

DECLARE

v_user varchar2(30);

begin

select user into v_user from dual;

dbms_output.put_line('User '||v_user||' Just updated a line');

end;

/

Update data

update heros set hero_name='Talha Zubair Khan' where hero_name='Talha Zubair';

Result:

1 row updated.

User HR Just updated a line

Create Insert, update, delete Trigger at a time.

code:

create or replace trigger tr_heros

before insert or delete or update on heros

for each row

enable

declare

v_user varchar2(30);

begin

Select user into v_user from dual;

if inserting then

dbms_output.put_line('User '||v_user||' Just Inserted A line');

elsif deleting then

dbms_output.put_line('User '||v_user||' Just deleted A line');

elsif updating then

dbms_output.put_line('User '||v_user||' Just Updated A line');

end if;

end;

/

Result:

Trigger TR_HEROS compiled

Now test the trigger.

inserting:

insert into heros values('Salman');

Result:

1 row inserted.

User HR Just Inserted A line

Updating:

delete from heros where hero_name='Salman';

Result:

1 rows deleted.

User HR Just deleted A line