Home » Other » General » Trigger execution shows up in DBA_Objects with execution time as Last_DDL_Time (Oracle Database SE 12.x)
Trigger execution shows up in DBA_Objects with execution time as Last_DDL_Time [message #678466] Fri, 06 December 2019 07:15 Go to next message
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 #678468 is a reply to message #678466] Fri, 06 December 2019 07:28 Go to previous messageGo to next message
BlackSwan
Messages: 26747
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Please post full TRIGGER code source
Re: Trigger execution shows up in DBA_Objects with execution time as Last_DDL_Time [message #678469 is a reply to message #678466] Fri, 06 December 2019 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67444
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
When ever I make a change in a table X that internally fires a trigger Y,
Do you mean when you modify a row in the table and the trigger is a DML trigger or when you alter the table and the trigger is a DDL one?

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 Go to previous messageGo to next message
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

Re: Trigger execution shows up in DBA_Objects with execution time as Last_DDL_Time [message #678501 is a reply to message #678492] Mon, 09 December 2019 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 67444
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

BlackSwan
This OP may have used this forum a WONR drive: Write Once Never Read.
Re: Trigger execution shows up in DBA_Objects with execution time as Last_DDL_Time [message #678776 is a reply to message #678466] Fri, 10 January 2020 01:17 Go to previous message
KrishnaGujar
Messages: 1
Registered: January 2020
Junior Member
Great information..

Previous Topic: Problem with Oracle supplied perl script
Next Topic: New to OCI, then I need some free little and simply projects
Goto Forum:
  


Current Time: Wed Oct 21 20:11:43 CDT 2020