• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

DML Trigger on system view: sys.triggers ?

Hi, MS SQL Experts,
Is there a way to create DML(UPDATE) trigger on system view: sys.triggers, or is there any other ways to monitor the column(is_disabled) change on this view?
(I am using MS SQL 2005)
Thanks,
-Rick
0
RHADMIN
Asked:
RHADMIN
  • 8
  • 7
1 Solution
 
chapmandewCommented:
0
 
RHADMINAuthor Commented:
DDL trigger(ON DATABASE/SERVER )won't work, it can't either audit system view or audit trigger state(ENABLED//DISABLED); it only works on CREATE_TIGGER, ALTER_TIGGER, DROP_TIGGER events.
0
 
chapmandewCommented:
If you enable the trigger, the DDL trigger would catch that.  You can't create triggers on system views.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
RHADMINAuthor Commented:
Do you have the TSQL as a working example(on AdventureWorks)? I tried, it didn't work.
0
 
RHADMINAuthor Commented:
Are there some other ways to audit changes on sys.triggers?
0
 
chapmandewCommented:
CREATE TRIGGER triggername
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
      SET NOCOUNT ON

      DECLARE @EventData XML
      SET @EventData = EVENTDATA()

      BEGIN TRY
            INSERT INTO audittable  --you have to create this
            (
                  AuditDate,
                  LoginName,
                  ObjectName,
                  EventType,
                  ServerName,
                  DatabaseName,
                  Command,
                  EventData
            )
            SELECT
                  GETDATE(),
                  t.c.value('(LoginName)[1]','VARCHAR(255)'),
                  t.c.value('(ObjectName)[1]','VARCHAR(255)'),
                  t.c.value('(EventType)[1]','VARCHAR(100)'),
                  t.c.value('(ServerName)[1]','VARCHAR(255)'),
                  t.c.value('(DatabaseName)[1]','VARCHAR(255)'),
                  t.c.value('(TSQLCommand/CommandText)[1]','NVARCHAR(MAX)'),
                  @EventData
            FROM      
                  @EventData.nodes('/EVENT_INSTANCE') t(c)
            WHERE
                  t.c.value('(EventType)[1]','VARCHAR(100)') NOT LIKE '%STATISTICS%'
      END TRY
      BEGIN CATCH
            PRINT 'ERROR'
      END CATCH

END
0
 
RHADMINAuthor Commented:
I've tested your script, it will not catch command like:
DISABLE TRIGGER tr_abc
ON DATABASE
0
 
chapmandewCommented:
it is a database trigger, not a server trigger.  a server trigger catches the disabliing of database level triggers.
0
 
chapmandewCommented:
the trigger i gave you is for catching DML triggers when created or altered on user tables or views.
0
 
RHADMINAuthor Commented:
I know ALTER TABLE statement can also be used to DISABLE TRIGGER, and this ALTER TABLE statement definitely catchable through DDL tigger, my original question is how to catch statement like:

DISABLE TRIGGER tr_abc
ON tbl_abc | DATABASE | ALL SERVER
0
 
chapmandewCommented:
You're not understanding me....  ALTER TABLE IS catchable through the DDL trigger I gave above.  The statement

DISABLE TRIGGER tr_abc
ON tbl_abc | DATABASE | ALL SERVER

is NOT the same as a trigger on a table...it isa  trigger on the database...and would be caught by a SERVER level trigger.  
0
 
RHADMINAuthor Commented:
Please try it out first, see if you can catch the event of statement below:

USE AdventuerWorks
DISABLE TRIGGER trigger_ABC
ON table_ABC

I have tried it, it just did not work with DISABLE TRIGGER statement(or ENABLE TRIGGER), but statements like CREATE TRIGGER, ALTER TRIGGER and DROP TRIGGER are all catched with no problem! I don't know why? it seems MS SQL 2005 is missing these 2 events.
0
 
chapmandewCommented:
0
 
RHADMINAuthor Commented:
Thanks, DISABLE TRIGGER statement turns out NOT CATCHable by any means so far!
0
 
chapmandewCommented:
yep.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now