|
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. |
|
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. 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 '20, 03:56)
Baron
Replies hidden
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 '20, 04:02)
Volker Barth
|
