SYNONYM Based TRIGGER Fire Even After SYNONYM is Dropped
(Doc ID 371813.1)
Last updated on MARCH 04, 2022
Applies to:
PL/SQL - Version 8.1.7.0 to 10.1.0.3 [Release 8.1.7 to 10.1]Information in this document applies to any platform.
Symptoms
Created a SYNONYM Based TRIGGER. Dropped the SYNONYM after the TRIGGER is created, but the TRIGGER remains valid and fires when TABLE is UPDATEd
Synonym Based TRIGGER is not invalidated After SYNONYM is Dropped.
1) Create tables and synonym:
create table CLAIMEXP (vno number);
create synonym EMC_CLAIMEXP for CLAIMEXP;
create table CLAIMEXP_UPDTS_FOR_ADHOC(vno number);
create synonym EMC_CLAIMEXP for CLAIMEXP;
create table CLAIMEXP_UPDTS_FOR_ADHOC(vno number);
2) Insert data:
SQL> insert into claimexp values(1000);
1 row created.
SQL> commit;
Commit complete.
1 row created.
SQL> commit;
Commit complete.
3) Create trigger:
SQL> CREATE OR REPLACE TRIGGER CLAIMEXP_AFTR_UPDT_TRG
AFTER UPDATE ON EMC_CLAIMEXP
FOR EACH ROW
DECLARE
insert_datetime DATE;
BEGIN
INSERT INTO CLAIMEXP_UPDTS_FOR_ADHOC
VALUES ( :NEW.vno );
END;
AFTER UPDATE ON EMC_CLAIMEXP
FOR EACH ROW
DECLARE
insert_datetime DATE;
BEGIN
INSERT INTO CLAIMEXP_UPDTS_FOR_ADHOC
VALUES ( :NEW.vno );
END;
4) check if trigger works:
SQL> select * from CLAIMEXP_UPDTS_FOR_ADHOC;
no rows selected
SQL> update claimexp set vno =2000;
1 row updated.
SQL> select * from CLAIMEXP_UPDTS_FOR_ADHOC;
VNO
2000
SQL> commit;
no rows selected
SQL> update claimexp set vno =2000;
1 row updated.
SQL> select * from CLAIMEXP_UPDTS_FOR_ADHOC;
VNO
2000
SQL> commit;
5) Drop the synonym:
SQL> drop synonym EMC_CLAIMEXP;
Synonym dropped.
Synonym dropped.
6) Even after the drop the trigger is valid and works as normal:
SQL> update claimexp set vno =2000;
1 row updated.
SQL> select * from CLAIMEXP_UPDTS_FOR_ADHOC;
VNO
2000
2000
SQL> select * from user_triggers where trigger_name = 'CLAIMEXP_AFTR_UPDT_TRG';
CLAIMEXP_AFTR_UPDT_TRG AFTER EACH ROW
UPDATE
SCOTT TABLE CLAIMEXP
REFERENCING NEW AS NEW OLD AS OLD
ENABLED
CLAIMEXP_AFTR_UPDT_TRG
AFTER UPDATE ON EMC_CLAIMEXP
FOR EACH ROW
PL/SQL
DECLARE
insert_datetime DATE;
BEGIN
INSERT INTO CLAIMEXP_UPDTS_FOR_ADHOC
VALUES ( :NEW.vno );
END;
SQL> select * from user_objects where object_name = 'CLAIMEXP_AFTR_UPDT_TRG';
CLAIMEXP_AFTR_UPDT_TRG
53699 TRIGGER
30-MAY-06 30-MAY-06 2006-05-30:14:46:30 VALID N N N
SQL> select * from user_objects where object_name = 'EMC_CLAIMEXP';
no rows selected
1 row updated.
SQL> select * from CLAIMEXP_UPDTS_FOR_ADHOC;
VNO
2000
2000
SQL> select * from user_triggers where trigger_name = 'CLAIMEXP_AFTR_UPDT_TRG';
CLAIMEXP_AFTR_UPDT_TRG AFTER EACH ROW
UPDATE
SCOTT TABLE CLAIMEXP
REFERENCING NEW AS NEW OLD AS OLD
ENABLED
CLAIMEXP_AFTR_UPDT_TRG
AFTER UPDATE ON EMC_CLAIMEXP
FOR EACH ROW
PL/SQL
DECLARE
insert_datetime DATE;
BEGIN
INSERT INTO CLAIMEXP_UPDTS_FOR_ADHOC
VALUES ( :NEW.vno );
END;
SQL> select * from user_objects where object_name = 'CLAIMEXP_AFTR_UPDT_TRG';
CLAIMEXP_AFTR_UPDT_TRG
53699 TRIGGER
30-MAY-06 30-MAY-06 2006-05-30:14:46:30 VALID N N N
SQL> select * from user_objects where object_name = 'EMC_CLAIMEXP';
no rows selected
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |