{"id":17164,"date":"2020-10-11T12:26:24","date_gmt":"2020-10-11T12:26:24","guid":{"rendered":"https:\/\/dbtut.com\/?p=17164"},"modified":"2020-10-11T15:30:12","modified_gmt":"2020-10-11T15:30:12","slug":"oracle-pl-sql-trigger","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2020\/10\/11\/oracle-pl-sql-trigger\/","title":{"rendered":"Oracle PL\/SQL Trigger"},"content":{"rendered":"

This article contains information about Oracle PL\/SQL Trigger, Types such as DML,DDL and Datase Triggers and examples.<\/p>\n

What is Trigger in Oracle PL\/SQL?<\/h3>\n

The structure used to run a specific command after or before various operations on the database is called a trigger.<\/p>\n

Triggers have the same structure as the procedures in PL \/ SQL.<\/p>\n

The most important feature that distinguishes the triggers from the procedures is that the triggers run automatically after an event.<\/p>\n

For example after or before an insert. The keywords to be used when creating a trigger are as follows.<\/p>\n

CREATE [OR REPLACE ] TRIGGER My_Trigger_Name\r\n{BEFORE | AFTER | INSTEAD OF }\r\n{INSERT [OR] | UPDATE [OR] | DELETE}\r\n[OF My_Column_Name]\r\nON My_Table_Name\r\n -- When you specify Referencing, then you can use this keyword in triggered sql statement. For Example->MyOld.DeletedRowId\r\n[REFERENCING OLD AS MyOld NEW AS MyNew] \r\n[FOR EACH ROW]\r\nWHEN (My_Condition)\r\nDECLARE\r\n   -- definitions\r\nBEGIN\r\n   -- commands\r\nEXCEPTION\r\n   -- exception handling\r\nEND;<\/pre>\n
Trigger Types in Oracle PL\/SQL<\/h2>\n
Triggers are operated in the following cases.<\/p>\n
DML Triggers\u2013 INSERT, UPDATE, DELETE<\/p>\n
DDL Triggers\u2013 CREATE, ALTER, DROP<\/p>\n
Database Triggers \u2013 SERVERERROR, LOGON, LOGOFF, STARTUP ve SHUTDOWN<\/p>\n
DML Triggers in Oracle PL\/SQL<\/h3>\n
It is often used to record transactions in DML operations.<\/p>\n
Before Insert Trigger in Oracle PL\/SQL<\/h4>\n
In the example below, it will print “New Product inserted.” on the screen before the record is inserterd to the Products table. Because this is before insert trigger.<\/p>\n
If we use AFTER<\/strong> instead of BEFORE<\/strong> as a keyword, then it will print “New Product inserted.” on the screen after the record is inserterd to the Products table.<\/p>\n
CREATE OR REPLACE trigger TRIGGER_NEW_PRODUCT\r\nBEFORE INSERT ON PRODUCTS\r\nBEGIN\r\n    DBMS_OUTPUT.put_line('New Product inserted.');\r\nEND;<\/pre>\n
Also there are triggers like BEFORE INSERT TRIGGER as below.<\/p>\n