Save differences from a table update into a seprate table

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)

Save differences from a table update into a seprate table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-07 : 11:24:47

Morgan writes "I'm trying to figure out a way to save what is in the Inserted and Deleted tables in a SQL Trigger so you can look and see what values were changed on an update.

I think I have gotten close but can't figure out the last part of it. I create a cursor to step though the names of the table that I am updating to get every single column name with something like:

DECLARE @name AS COLUMN, @newValue AS VARCHAR(1000), @oldValue AS VARCHAR(1000), @mfrId AS INT, @s AS VARCHAR(1000)

DECLARE updateCursor CURSOR FOR
SELECT name FROM sysColumns WHERE id = (SELECT id FROM sysObjects WHERE name = 'myTable')
OPEN updateCursor

FETCH NEXT FROM updateCursor INTO @name
WHILE @@fetch_status = 0
BEGIN
'Can't figure out what goes here
FETCH NEXT FROM updateCursor INTO @name
END

CLOSE updateCursor
DEALLOCATE updateCursor

END

If I try to do a SELECT @name FROM inserted all it returns is the value in @name where I want the value of that column from the inserted table. I building it into a string but then the inserted table becomes an unknown table.

Any ideas?"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-06-07 : 11:30:23

cursors in trigers are a no no.

in the inserted and deleted pseudo-tables are all rows that were modified.

a trigger IS NOT fired for each changed row.
It's fired ONCE per modification.

so if you update 1, 10 or 100 rows at once a trigger will fire only once.
and deleted/inserted tables will hold 1, 10 or 100 rows respectivly.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-07 : 11:47:48

insert into History
select ins.field1 from inserted ins

insert into History
select del.field1 from deleted del

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-07 : 12:10:35

This sounds like an audit trail request. If so, there's no point reinventing the wheel...

http://www.nigelrivett.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html
http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html
http://www.google.co.uk/search?hl=en&q=tsql+audit+trail+triggers

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources