oracle trigger after insert example | Oracle After insert/update/delete Trigger - oracle tutorial - sql tutorial
How to trigger AFTER INSERT/UPDATE or DELETE in Oracle ?
- A table with various constraints to ensure data integrity.
- In addition to the constraints, I have triggers on BEFORE INSERT and BEFORE UPDATE to ensure that necessary data goes into the table.
- In some cases, allowing the application layer to omit the information from their queries, and in some cases forcing that it be present.
- This statement specifies that Oracle will fire this trigger AFTER the INSERT/UPDATE or DELETE operation is executed.
Syntax
CREATE [ OR REPLACE ] TRIGGER trigger_name
INSERT or UPDATE or DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
variable declarations
BEGIN
trigger code
EXCEPTION
WHEN ...
exception handling
END;
click below button to copy the code. By - oracle tutorial - team
Parameters
OR REPLACE:
- It is an optional parameter. It is used to re-create the trigger if it already exists. It facilitates you to change the trigger definition without using a DROP TRIGGER statement.
trigger_name:
- It specifies the name of the trigger that you want to create.
AFTER INSERT or UPDATE or DELETE:
- It specifies that the trigger will be fired after the INSERT or UPDATE or DELETE operation is executed.
table_name:
- It specifies that the trigger will be fired after the INSERT or UPDATE or DELETE operation is executed.
- It specifies the name of the table on which trigger operation is being performed.
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql
Limitations
- AFTER trigger cannot be created on a view.
- You cannot update the OLD values.
- You can only update the NEW values.
Oracle AFTER Trigger Example
- Consider, you have a "suppliers" table with the following parameters.
CREATE TABLE "SUPPLIERS"
( "SUPPLIER_ID" NUMBER,
"SUPPLIER_NAME" VARCHAR2(4000),
"SUPPLIER_ADDRESS" VARCHAR2(4000)
)
/
You can use the following CREATE TRIGGER query to create a AFTER INSERT
or UPDATE or DELETE Trigger:
CREATE OR REPLACE TRIGGER "SUPPLIERS_T2"
AFTER
insert or update or delete on "SUPPLIERS"
for each row
begin
when the person performs insert/update/delete operations into the table.
end;
/
ALTER TRIGGER "SUPPLIERS_T2" ENABLE
/
click below button to copy the code. By - oracle tutorial - team
- Here the trigger name is "SUPPLIERS_T2" and it is fired AFTER the insert or update or delete operation is executed on the table "suppliers".
