Newbie Needs Help with Trigger

  • Here is the situation:

    -User updates a record.

    -Before the record is updated, I need to insert a 'before' record to the HISTORY table.

    -Then I need to go ahead with the update.

    Can anyone provide basic code to do this? I know this much:

    CREATE TRIGGER Ticket_Update ON [dbo].[CSDTicket_Info] FOR UPDATE, DELETE AS

    And I also know that I have 2 virtual tables available:

    1) deleted (before image of the Ticket_Info record) and

    2)inserted (after image of the Ticket_Info record).

    Thanks!

  • create trigger MyTrigger for update

    as

    insert into historytable (fieldA, fieldB)

    select FieldA, FieldB

    from inserted

    return

    Steve Jones

    steve@dkranch.net

  • Like this

    CREATE TRIGGER dbo.tr_Name ON dbo.CSDTicket_Info

    FOR UPDATE, DELETE AS

    INSERT INTO CSDHistory (List.of.columns.with.data.to.fill) SELECT list.to.match.plus.any.variables.such.as.HOST.or.USERNAME.but.defaults.like.GETDATE().I.would.put.on.the.history.table.as.default.for.the.column.they.go.in FROM deleted

    Hope that makes sense.

  • One thing I always add as a habit is "set nocount on" as the first executable line in the trigger. I'd also suggest a naming convention just to make your life easier - I normally do something like this:

    create trigger ud_ticket_info on ticket_info

    One other point that I see a lot of people trip on is if you have an identity column in both the real table and the history table AND you are using @@Identity, it will return the value from the history table since it was the last insert completed. In SQL2K you can and should use Scope_Identity() to avoid this headache.

    Andy

  • Since your new to triggers and it looks like you're dealing with a ticketing system, one thing to be aware of is that triggers in SQL Server 7 and the AFTER trigger in SQL Server 2000 do not handle text, ntext, and image fields with respect to the inserted and deleted tables. Columns of these type cannot be used. I don't know if that's the case with your table, but I know we get that question every so often here.

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

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

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