Problem with a trigger... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with a trigger...

Author

Topic

Mr_V
Starting Member

3 Posts

Posted - 2005-11-02 : 06:25:59

Hello all,

My first post here, I hope I placed it under the right subforum.

We have recently started using SQL Server 2005 as a move from Sybase, so we're not all that up to speed with it yet.

A very simple example: we have a table, let's call it "DayTransaction", with columns "TranNumber", "UserID".

We have another table called "DayTransaction_Log", which is a copy of "DayTransaction", plus columns "HistTime" and "SystemUser".

First table should have a trigger that, on each update of any row, copies the entire row from "DayTransaction" into "DayTransaction_Log" plus two extra columns. We created this trigger as some of our reading sources suggested...


CREATE TRIGGER [LogTransactionUpdate] ON dbo.DayTransaction 
FOR UPDATE
AS
declare @TranNumber char(20)
declare @UserID char(8)
declare @HistTime DateTime
declare @SystemUser char(8)
BEGIN
   select @TranNumber = (select TranNumber from Inserted)
   select @UserID = (select UserID from Inserted)
...
   insert into WarrantLog values (@TranNumber, @UserID...)
END

And that's it. Very simple - select the column from the row that got changed, keep it in a variable, put it in insert statement for the log table.

However, we run into problems when we do a mass update - when a statement updates more than 1 row, as the subqueries of the trigger (in red colour above) return more than 1 row.

Any suggestions on this?

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-02 : 06:39:34

Instead of

select @TranNumber = (select TranNumber from Inserted)
select @UserID = (select UserID from Inserted)
insert into WarrantLog values (@TranNumber, @UserID...)

Try this

Insert into WarrantLog Select TranNumber ,UserID from Inserted

Madhivanan

Failing to plan is Planning to fail

Mr_V
Starting Member

3 Posts

Posted - 2005-11-02 : 07:10:02

Beauty!!!

Thanks mate!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-02 : 07:29:54

Well. See more information on Triggers in Books On Line, SQL Server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources