The forum will be down for maintenance at some point between Friday, September 25, 2020 at 5pm PDT and Sunday, September 27, 2020 at 11:59 PDT. Downtime is unknown but will be minimized.

I have created a trigger TRG1 on one table TBL1 and for unknown reason is this trigger disappeared!!

Is there a way to trace the creation and dropping date of TRG1?

I tried:

select * from sysobject where object_id = (select object_id from systrigger where trigger_name = 'TRG1' and table_id = (select table_id from systable where table_name = 'TBL1') );

But now is too late, because after dropping the trigger remains nothing in SYSTRIGGER, SYSOBJECT!!

N.B. dropping the trigger may have been done with DROP Statement, not with CREATE OR REPLACE, since the used database is SQL10.

asked 10 Jun, 02:50

Baron's gravatar image

Baron
1.2k4769110
accept rate: 28%

edited 10 Jun, 02:51


Have you dropped the table the trigger belongs to? Then the trigger will be dropped automatically, as well.

Otherwise, the transaction log (the active or older ones) should contain a DROP TRIGGER statement then, and DBTRAN should reveal that statement and when and by whom it was executed.

That a dropped object is not contained in the system catalog should not come as a surprise, that is true for all database objects: When they have been dropped, the information is gone. This is similar to rows deleted from a user table, they are gone, as well.


As to "Tracing" (or "tracking") the creation and deletion of database objects: You can use the builtin Auditing facility for that purpose, and/or there have been various discussions on using user-defined "tracking tables" for DDL statements - you may search for "DDL triggers" and similar keywords.

permanent link

answered 10 Jun, 03:46

Volker%20Barth's gravatar image

Volker Barth
36.7k343505761
accept rate: 34%

edited 10 Jun, 03:57

No, I didn't drop the table to which the trigger belongs!!

This drop may have been done several months ago, and the trnasaction log of that date doesn't exist anymore!

I wanted to ask this question, because the same thing I have experienced in Mobilink Scripts, and there (by Mobilink), the old versions of mobilink scripts remain in ML_SCRIPT table even after several times changing it.

Thanks anyway, and I close the point.

(10 Jun, 03:56) Baron
Replies hidden

in Mobilink Scripts, and there (by Mobilink), the old versions of mobilink scripts remain in ML_SCRIPT table even after several times changing it.

Well, ML scripts explicitly do use "versions" because different remotes may need to use different script versions at the same time, and so it is up to you to delete older script versions when they are no longer needed. But for database objects there is only "one valid version" for all users, the one currently defined in the system catalog.

(10 Jun, 04:02) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×73
×8
×2

question asked: 10 Jun, 02:50

question was seen: 110 times

last updated: 10 Jun, 04:04