February 8, 2002 at 4:23 pm
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!
February 8, 2002 at 4:42 pm
create trigger MyTrigger for update
as
insert into historytable (fieldA, fieldB)
select FieldA, FieldB
from inserted
return
Steve Jones
February 8, 2002 at 4:42 pm
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.
February 8, 2002 at 6:12 pm
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
February 8, 2002 at 7:26 pm
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
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