Post reply

Script Triggers

  • August 24, 2007 at 8:35 am

    #70655

    How to script triggers in SQL Server? I just need to script triggers without tables. Is there a way around?

  • August 24, 2007 at 10:51 am

    #729343

    Under SQL Server 2000 and 2005 you can get the trigger creation scripts from the syscomments system table. If the trigger content is over 4000 bytes, the definition will be split over several lines. You can filter the rows for the triggers by checking the objectid, and whether it blongs to a trigger like WHERE OBJECTPROPERTY(c.id, 'IsTrigger') = 1

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 24, 2007 at 2:22 pm

    #729373

    look into SQL Server 2000 BOL triggers, creating

  • August 24, 2007 at 2:39 pm

    #729377

    here's an example to get teh text/definition of the triggers:

    set nocount on

     SELECT [text] + CHAR(13) FROM syscomments WHERE id IN(SELECT id FROM sysobjects WHERE xtype='TR')

     

    typical results:

    CREATE TRIGGER updatedby ON dbo.gmact

    FOR INSERT, UPDATE

    AS

    IF UPDATE(addr1)

    BEGIN

    UPDATE c

     SET c.addr2 = i.addr1

    FROM inserted i

    INNER JOIN dbo.gmact c ON i.acttblkey = c.acttblkey

    END

    GO

    etc.....

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply