Search your topic here

Create a trigger

CREATING A TRIGGER:

Trigger can be created using the create trigger command.
Syntax-


CREATE (OR REPLACE) TRIGGER trigger_name
[BEFORE|AFTER]
[DELETE|INSERT|UPDATE[ OF column_name]]
ON table_name
[FOR EACH ROW][WHEN condition]
[PL/SQL BLOCK];
For example:


Program of trigger for converting lowercase to uppercase before insert. Name it as trig.sql.
CREATE or REPLACE trigger upper1 BEFORE INSERT on STUDENT
For each ROW
BEGIN
:new.stdname:=upper (:new.stdname);
END;
Than compile above trigger using:
SQL>GET TRIG.SQL;
SQL>/


After compilation this message will display:
Trigger is created successfully.


Now we can insert the value in table student.
SQL> INSERT INTO student (stdname) VALUES (‘Jayesh’);


Now we will run SELECT query to see the table.
SQL> SELECT stdname FROM student;

STUDENT
stdname
JAYESH

Trigger example 2:
TRIG1.SQL

Program of trigger for after insert. To increment the serial number automatically after inserting the new student record.

CREATE or REPLACE trigger after_trigger1 AFTER INSERT ON student
For each ROW
BEGIN
UPDATE student SET sno - sno+1 where stdname = new.stdname;
END;

Trigger example 3:
TRIG2.SQL

Program of trigger for after delete. To decrement the serial number automatically after deleting the student record from table student.


CREATE or REPLACE trigger after_delete1 AFTER DELETE ON student
For each ROW
BEGIN
UPDATE student SET sno = sno-1 where stdname=:old.stdname;
END;

LEAVE A REPLY










Related topics

Please use contact page in this website if you find anything incorrect or you want to share more information about the topic discussed above.