Post reply

Trigger Raised Before Deleted For Each Row

  • October 13, 2009 at 7:06 am

    #135766

    :-PHai to all;

    I Need help for Raising Trigger;

    1. How To Create a Trigger in Before Delete For Each Row;

    For Ex

    Create table Test( Id int,name varchar(15) -- master Table

    create table Trans(id int,name varchar(15),Owner varchar(10)

    inert into test values (1,'Swift')

    insert into test values (2,'Honda')

    -----

    In case Delete(OR) update the First id in '1' in master table ;

    This is Replicate the Tranaction Tables;

    and also To Create Log For Each Tranaction

    Log Table Like That

    Sno : Userid(Sqlserver userid) : IP Address (To Perform the Operation in Given the System) : Operation ( Like Insert or Update or Delete)

    To Perform Only Trigger operation

    Pls Any One Help This ;

    Thanks Advance;

    A.Faijurrahuman M.C.A

    Software Programmar

    Coimbatore

  • October 13, 2009 at 7:14 pm

    #1065462

    To trap an event before it happens you have to use INSTEAD OF in your trigger. The syntax is readily available in the help on line for CREATE TRIGGER.

    However, if all you want to do is log the deletions, consider using the OUTPUT clause instead. It gives you access to the deleted table which contains the set of rows deleted. You could use this set as the source for your update.

    declare @sample table (rowID int identity(1,1), name varchar(10))

    declare @log table (rowID int, name varchar(10))

    insert into @sample

    select 'Anna' union all

    select 'Betty' union all

    select 'Cathy'

    select *,'Before' from @sample

    BEGIN TRANSACTION

    delete @sample

    OUTPUT deleted.rowID,deleted.name into @log

    where name != 'Betty'

    -- now do updates to other tables and permanent logging based on contents of @log

    COMMIT TRANSACTION

    select *,'After' from @sample

    select *,'Log' from @log

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • October 13, 2009 at 10:12 pm

    #1065488

    Hai Bob;

    Thank u so much;

    I will try it;

  • October 15, 2009 at 4:17 am

    #1066158

    Hai To Every one;

    How Can I Use the Column_isupdated() Function

    pls Help This

    Thanks Advance ;

    A.Faijur

  • October 15, 2009 at 4:27 am

    #1066161

    Please google first (3rd result for "trigger updated")

    http://www.sqlservercentral.com/articles/Triggers/triggerscolumnsupdated/115/


    Clear Sky SQL
    My Blog[/url]

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

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