Trigger execution shows up in DBA_Objects with execution time as Last_DDL_Time [message #678466] |
Fri, 06 December 2019 07:15 |
|
ranjithcnair.in
Messages: 1 Registered: December 2019
|
Junior Member |
|
|
Hi Experts,
I'm admin for an application which connects to Oracle Database SE 12.x.
When ever I make a change in a table X that internally fires a trigger Y, I'm seeing DBA_Objects getting updated for the trigger and the table name with Last_DDL_Time as current time.
Is firing of a DB trigger Y considered as a DDL and is it because the trigger is for Table X that both these objects show up in DBA_Objects with Last DDL_Time as current time?
Thanks in advance for the support on this.
Regards
Ranjith
|
|
|
|
|
Re: Trigger execution shows up in DBA_Objects with execution time as Last_DDL_Time [message #678492 is a reply to message #678469] |
Mon, 09 December 2019 08:11 |
|
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
I'm not seeing the behavior in 12.2.0.1.0. I think your trigger is doing something more than DML.
DEV1> CREATE TABLE MYTABLEA (ID NUMBER, SOMEDATA VARCHAR2(80));
Table created.
DEV1> CREATE TABLE MYTABLEB (ID NUMBER, SOMEDATA VARCHAR2(80));
Table created.
DEV1>
DEV1> CREATE TRIGGER MYTABLEA_TRIG BEFORE INSERT ON MYTABLEA FOR EACH ROW
2 BEGIN
3 INSERT INTO MYTABLEB VALUES (:NEW.ID, :NEW.SOMEDATA);
4 END;
5 /
Trigger created.
DEV1>
DEV1> SELECT OBJECT_NAME, LAST_DDL_TIME FROM USER_OBJECTS WHERE OBJECT_NAME LIKE 'MYTABLE%' AND OBJECT_TYPE = 'TABLE';
OBJECT_NAME LAST_DDL_TIME
------------------------------ -------------------
MYTABLEA 2019-12-09 09:24:20
MYTABLEB 2019-12-09 09:24:20
2 rows selected.
DEV1>
DEV1> INSERT INTO MYTABLEA VALUES (0, '0');
1 row created.
DEV1> COMMIT;
Commit complete.
DEV1> EXEC DBMS_LOCK.SLEEP(5);
PL/SQL procedure successfully completed.
DEV1>
DEV1> SELECT OBJECT_NAME, LAST_DDL_TIME FROM USER_OBJECTS WHERE OBJECT_NAME LIKE 'MYTABLE%' AND OBJECT_TYPE = 'TABLE';
OBJECT_NAME LAST_DDL_TIME
------------------------------ -------------------
MYTABLEA 2019-12-09 09:24:20
MYTABLEB 2019-12-09 09:24:20
2 rows selected.
JP
[Updated on: Mon, 09 December 2019 08:25] Report message to a moderator
|
|
|
|
|