Database: Oracle 11g.
Connect to Oracle 11g –
Open Query Analyzer
1] Create Table ORDERS
CREATE TABLE "SYSTEM"."ORDERS" (ORD_ID NUMBER,
ITEM_CODE VARCHAR2(100),
ORD_QTY NUMBER,
ORD_DATE DATE)
GO
2] Create Table ORDER_ARCHIVE
CREATE TABLE "SYSTEM"."ORDER_ARCHIVE"(ORD_ID NUMBER,
ORD_CODE VARCHAR2(100))
Go
3] Create Trigger TRG_ORDERS
CREATE OR REPLACE TRIGGER "SYSTEM"."TRG_ORDERS"
BEFORE INSERT ON "SYSTEM"."ORDERS"
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Insert order‘||:NEW.ORD_ID||’ into ORDER_ARCHIVE');
INSERT INTO "SYSTEM"."ORDER_ARCHIVE" VALUES (:NEW.ORD_ID,:NEW.ITEM_CODE);
END;
GO
4] Now Insert values in table ORDERS
INSERT INTO "SYSTEM"."ORDERS"("ORD_ID", "ITEM_CODE", "ORD_QTY", "ORD_DATE")
VALUES(1, 'S', 1, SYSDATE)
GO
Trigger is fired successfilly gives output as below
DBMS_OUTPUT:
------------
Insert order‘||:NEW.ORD_ID||’ into ORDER_ARCHIVE
5] Now run select query for table ORDER_ARCHIVE
SELECT * FROM "SYSTEM"."ORDER_ARCHIVE"
GO
We can see data inserted in a table ORDER_ARCHIVE
6] Now disable trigger
ALTER TRIGGER "SYSTEM"."TRG_ORDERS" DISABLE
7] Now again insert values in a table ORDERS
INSERT INTO "SYSTEM"."ORDERS"("ORD_ID", "ITEM_CODE", "ORD_QTY", "ORD_DATE")
VALUES(2, 'T', 5, SYSDATE)
GO
This time data is not inserted in a table ORDER_ARCHIVE,It inserts only in Table ORDERS.
8] To check , run select query for table ORDER_ARCHIVE
SELECT * FROM "SYSTEM"."ORDER_ARCHIVE"
GO
It shows previous data only.
That means trigger is disable successfully.
9] Now enable trigger
ALTER TRIGGER "SYSTEM"."TRG_ORDERS" ENABLE
10] Insert values in ORDERS
INSERT INTO "SYSTEM"."ORDERS"("ORD_ID", "ITEM_CODE", "ORD_QTY", "ORD_DATE")
VALUES(3, 'R', 6, SYSDATE)
GO
11]Again if we run following query
SELECT * FROM "SYSTEM"."ORDER_ARCHIVE"
GO
we observed data inserted successfully in table ORDER_ARCHIVE
But when we try it to disable or enable triggers from tree node we will notice following things.
A] Initially Trigger is in enabled state,but icon for enable state is not matching with the icon provided on Right Click Menu.
B] When we right click on trigger "TRG_ORDERS" and select option ‘Disable Trigger’,It disables the trigger(verified through Query Analyser) but icon does not changes from enable to disable.It remains same.
