QA Update and an Update Trigger

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)

QA Update and an Update Trigger

Author

Topic

CartesianJoin
Starting Member

11 Posts

Posted - 2004-05-05 : 18:38:02

I have an update trigger on a table. It is an account table listing our accounts. The trigger was intended to create an audit trail. One column, ParentID, is used to determine which salesman owns the account. The trigger fires when this column is changed, via the update trigger.

I was asked to do some mass account re-assignments and my sql statement does the update in one statement via QA, encompassing numerous records. The update trigger only records the details of the last line touched, not all lines touched. Does the trigger treat an update statement as one update, even if it covers many rows? My table only shows one audit entry even though at least 20000 rows were updated.

Do I need to wrap this in a cursor or is there a tsql directive I can use to make the trigger fire on every row update?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-05 : 19:06:22

You are probably using variables in your trigger. A variable can only hold one value at a time. The update trigger handles multiple rows in the inserted table. Post your trigger code and we'll show you where the problem is.

Using a cursor in a trigger (or anywhere for that matter) would not be recommended since cursors are slow and the query would take longer to execute.

Tara

CartesianJoin
Starting Member

11 Posts

Posted - 2004-05-05 : 19:22:44

Thanks for the reply. I am not planning on using a cursor in the trigger and I only use cursors if imperative. Trigegr code below. Thanks for any suggestions you might have:


Create Trigger TransferClient
On Account
For Update
As
If Update(ParentID)
	Begin
		Declare @NewAgent char(12),
		@AccountID char(12),
		@OldAgent char(12)
		
		
		Select @AccountID = AccountID, @OldAgent = ParentID
		From Deleted

		Select @NewAgent = ParentID
		From Account
		Where AccountID = @AccountID
		
		Insert C_AccountMove_Audit
		(OldAgent, NewAgent, MoveDate, MoveUser, AccountID)
		values
		(@OldAgent, @NewAgent, GetDate(), App_Name(), @AccountID)
	End

CartesianJoin
Starting Member

11 Posts

Posted - 2004-05-05 : 19:32:50

I just reread your reply. If I eliminate the vars by using this sql in the trigger, do you thunk I will be successful?


Begin
		Declare @NewAgent char(12),
		@AccountID char(12),
		@OldAgent char(12)
		
		
		Select @AccountID = AccountID, @OldAgent = ParentID
		From Deleted

		Select @NewAgent = ParentID
		From Account
		Where AccountID = @AccountID
		
		Insert C_AccountMove_Audit
		(OldAgent, NewAgent, MoveDate, MoveUser, AccountID)
		Select D.ParentID, N.ParentID, GetDate(), App_Name(), N.AccountID
		From Account N
		Inner Join Deleted D On (N.AccountID = D.AccountID)
		--values
		--(@OldAgent, @NewAgent, GetDate(), App_Name(), @AccountID)
	End

CartesianJoin
Starting Member

11 Posts

Posted - 2004-05-05 : 19:34:51

I have tested it and it appears I have solved this with your help Tara, unless you can see another problem. I thank you so much for your attention.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-05 : 19:34:55

This should give you what you are looking for:

Create Trigger TransferClient
On Account
For Update
As
If Update(ParentID)
Begin
Insert C_AccountMove_Audit
(OldAgent, NewAgent, MoveDate, MoveUser, AccountID)

Select d.ParentID, a.ParentID, GetDate(), App_Name(), d.AccountID
From
Deleted d
INNER JOIN Account a ON d.AccountID = a.AccountID

End

Looks like we posted at the same time.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

CartesianJoin
Starting Member

11 Posts

Posted - 2004-05-05 : 19:37:12

Final copy on the trigger if anyone is interested:


Create Trigger TransferClient
On Account
For Update
As
If Update(ParentID)
	Begin
		Insert C_AccountMove_Audit
		(OldAgent, NewAgent, MoveDate, MoveUser, AccountID)
		Select D.ParentID, N.ParentID, GetDate(), App_Name(), N.AccountID
		From Account N
		Inner Join Deleted D On (N.AccountID = D.AccountID)
	End

GO

And of course a thank you to Derrick as well for his attention to my problem :)

R_Conover
Starting Member

1 Post

Posted - 2004-05-20 : 11:57:15

I am dealing with a problem like this, and me Update trigger is used to audit changes to fields as well.
The trigger code I am using is very much like the Account example here

My need is to show the Original Value(Deleted) and the New Value(Inserted).

This is easy to do by joining Inserted & Deleted by a Primary Key.
** Primary Key is not a Unique Identifier, but actualy a Unique constrant on 1 or more columns in the table

My problem is......

What if one of the fields in the Unique constraint is changed?
The Join Fails and no record is inserted.

Any Thoughts

Kristen
Test

22859 Posts

Posted - 2004-05-21 : 12:28:09

quote:
My need is to show the Original Value(Deleted) and the New Value(Inserted).

I just store ALL the rows in DELETED (i.e. no JOIN to INSERTED or the actual table.)

I figure that the current value is in the actual table, and the next/previous value for any particular "edit" is the adjacent row in the AUDIT table (matched on PK).

If your PK might change you could add a few extra columns to the AUDIT table for an IDENTITY and a PREVIOUS link; when a new ROW is INSERTed into AUDIT you'd need to do an OUTER JOIN to the newest matching row already in AUDIT and put its ID value into the PREVIOUS_ID column of the new row.

By the by, we also put a DATETIME column on the AUDIT table to show when it was deleted; other than that the AUDIT has all the same columns as the main table, but with no constraints. If we reduce the width of a column we leave the AUDIT table column at the biggest size it has ever been to prevent truncation.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources