Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Trigger that uses a Parameter with Inserted/Deleted Expand / Collapse
Author
Message
Posted Wednesday, November 5, 2008 9:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 24, 2009 2:39 PM
Points: 1, Visits: 16
Objective:
Loop through any table, using a trigger and determine what fields have changed within the table, and insert what was changed into another table.

I've tried a Cursor and a While Loop, but this doesn't really matter in reference to the output.

i.e. within the Cursor or While loop I have this....

--this comes back with a list of fields within the table
SELECT sc.name AS fldname FROM sys.sysobjects

OPEN CursorList
FETCH NEXT FROM CursorList INTO @FieldName

WHILE @@fetch_status = 0
BEGIN
DECLARE @SQL NVARCHAR(500)

--here comes the fun part, it's not taking the value of the Field, it's bringing back "FieldName1", which is the name of my field in the table. Currently, in my table, I have a value of "10" in FieldName1. When I change it to "11", I expect for my FieldName for Deleted to be "10" and my FieldName for Inserted to be "11"
--I don't want to Hardcode the fieldname i.e.
--Set @NewFieldName = (Select FieldName1 From Inserted)
--This defeats the purpose

--I'm open to other suggestions

SET @SQL=''
SELECT @SQL=@SQL+'Set @PrevValue = ''' + @FieldName + '''; ' FROM Deleted
EXECUTE sp_executesql @SQL, N'@PrevValue Varchar(max) Output', @PrevValue Output

SET @SQL=''
SELECT @SQL=@SQL+'Set @NewValue = ''' + @FieldName + '''; ' FROM Inserted

EXECUTE sp_executesql @SQL, N'@PrevValue Varchar(max) Output', @PrevValue Output

EXECUTE sp_executesql @SQL, N'@NewValue Varchar(max) Output', @NewValue Output
Post #597479
Posted Wednesday, November 5, 2008 9:56 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:36 AM
Points: 870, Visits: 896
There's only one thing I find scarier than a cursor, and that's a cursor that is used inside of a trigger.

Trust me, you do NOT want to go down this route. I know this isn't what you want to hear and I know why you don't want to hardcode field names in your trigger, but your reasons for not wanting to do so are in direct conflict with the performance of your database.

You could create a "trigger creation script", into which you pass a table name as a parameter and it constructs a standard trigger template for that table and its columns.

Then all you have to do is run that script for all of the tables you want triggers on.


Karl
source control for SQL Server
Post #597500
Posted Wednesday, November 5, 2008 10:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
I'm fairly sure you can't do what you're trying to do because executing your dynamic sql will open a new connection under which the virtual inserted/deleted tables do not exist. Can you explain a little better exactly what your issue is and what you want your end result to be?

Seth Phelabaum
Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #597508
Posted Wednesday, November 5, 2008 10:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:04 AM
Points: 1,865, Visits: 591
have a look a this script for a generic audit trigger:

http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html

perhaps this addresses your issue?

Tom


Life: it twists and turns like a twisty turny thing
Post #597510
Posted Wednesday, November 5, 2008 10:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 12,903, Visits: 31,972
if you think you need a cursor in a trigger, you're always, and i mean ALWAYS, looking at the problem wrong.

one of the new feature sin SQL2005 is the COLUMNS_UPDATED function, where you can say, audit changes only of columns 2,4,5 or 6 in your table have been changed.

here's an example to play with:
CREATE TABLE MYADDRESSES(
EMPNO INT IDENTITY(1,1) PRIMARY KEY,
ENAME VARCHAR(100), --column 2 will not track changes
ADDR1 VARCHAR(100),
ADDR2 VARCHAR(100),
CITY VARCHAR(100),
STATECODE VARCHAR(2),
ZIPCODE VARCHAR(100),
PHONE VARCHAR(20),
MOREDATA VARCHAR(100)) --column 9 will not track changes

CREATE TABLE ADDRESSCHANGES(
EMPNO INT ,
COLCHANGEDBITMASK INT, --example: saved so you can audit which cols actually changed, but they are both here anyway!
OLDENAME VARCHAR(100),
OLDADDR1 VARCHAR(100),
OLDADDR2 VARCHAR(100),
OLDCITY VARCHAR(100),
OLDSTATECODE VARCHAR(2),
OLDZIPCODE VARCHAR(100),
OLDPHONE VARCHAR(20),
NEWENAME VARCHAR(100),
NEWADDR1 VARCHAR(100),
NEWADDR2 VARCHAR(100),
NEWCITY VARCHAR(100),
NEWSTATECODE VARCHAR(2),
NEWZIPCODE VARCHAR(100),
NEWPHONE VARCHAR(20))
GO
--modified from http://www.sqlservercentral.com/Forums/Topic593727-148-1.aspx#bm595054
CREATE TRIGGER TR_MYADDRESSES
ON MYADDRESSES
AFTER UPDATE AS
/*Check whether columns 2 thru 8 have been updated. If any or all
columns 2, 3 or 4 have been changed, create an audit record. The
bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To test
whether all columns 2, 3, and 4 are updated, use = 14 instead of > 0
(below).
this one specifically is for 3 thru 8 is:
select power(2,(3-1))
+ power(2,(4-1))
+ power(2,(5-1))
+ power(2,(6-1))
+ power(2,(7-1))
+ power(2,(8-1)) = 252*/
--IF (COLUMNS_UPDATED()) <> 0
IF (substring(COLUMNS_UPDATED(),1,1) & 252 )>0
BEGIN

--inside a trigger, two special tables exist for the duration of the trigger:
--the table INSERTED and the table DELETED
--an UPDATE would have data in both tables...the value WAS DELETED to be replaced with the value from INSERTED

INSERT INTO ADDRESSCHANGES(EMPNO, COLCHANGEDBITMASK, OLDENAME, OLDADDR1, OLDADDR2, OLDCITY, OLDSTATECODE, OLDZIPCODE, OLDPHONE, NEWENAME, NEWADDR1, NEWADDR2, NEWCITY, NEWSTATECODE, NEWZIPCODE, NEWPHONE)
SELECT
DELETED.EMPNO,
COLUMNS_UPDATED(),
DELETED.ENAME,
DELETED.ADDR1,
DELETED.ADDR2,
DELETED.CITY,
DELETED.STATECODE,
DELETED.ZIPCODE,
DELETED.PHONE,
INSERTED.ENAME,
INSERTED.ADDR1,
INSERTED.ADDR2,
INSERTED.CITY,
INSERTED.STATECODE,
INSERTED.ZIPCODE,
INSERTED.PHONE
FROM DELETED
INNER JOIN INSERTED
ON DELETED.EMPNO = DELETED.EMPNO
END;
GO
--insert some test data
INSERT INTO MYADDRESSES( ENAME, ADDR1, ADDR2, CITY, STATECODE, ZIPCODE, PHONE, MOREDATA)
SELECT 'Kalvin','123 My Imagination St','','Miami','FL','33024','555-1212','likes snowmen'
UNION
SELECT 'Hobbes','123 My Imagination St','','Miami','FL','33024','555-1222','likes to tease calvin'

--renaming Calvin doesn't raise the trigger
UPDATE MYADDRESSES SET ENAME='Calvin' where ENAME='Kalvin'
--changing the Address DOES:
UPDATE MYADDRESSES SET ADDR1='123 G.R.O.S.S. Clubhouse' where ENAME='Calvin'
--changing multiple rows at the same time does trigger
UPDATE MYADDRESSES SET STATECODE='NY'
--setting the value to the same value repeating a previous insert:
UPDATE MYADDRESSES SET ADDR1='123 G.R.O.S.S. Clubhouse' where ENAME='Calvin'

select * from MYADDRESSES
select * from ADDRESSCHANGES


drop trigger TR_MYADDRESSES
drop table MYADDRESSES
drop table ADDRESSCHANGES



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #597519
Posted Wednesday, November 5, 2008 10:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:02 PM
Points: 7,135, Visits: 15,140
hodgy (11/5/2008)
have a look a this script for a generic audit trigger:

http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html

perhaps this addresses your issue?

Tom


Which will go "BOOM" against any of the "uglier" column types (TEXT/NTEXT, or any of the BLOB types in the newer editions....)

I never did quite get the reasoning for subjecting your server to such torture. Capturing a snapshot of the old row as it was is painless (compared to this), so bringing a server to its knees in order to order your log in a format like this (which you can achieve through a report) just doesn't work for me.

Personally speaking - if you're hell bent on having your final audit table in this way - I'd suggest you generate this particular format off-line. Just snapshot the changed rows in the trigger, then script it out to a "permanent audit table" during some scheduled process running outisde of the trigger.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #597557
Posted Wednesday, November 5, 2008 9:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
I agree with Matt... dynamic, supposedly "smart" triggers whose code is exactly the same for every table is "Death by SQL" and deserves a bucket load of high speed pork chops.

If you really want to be clever especially because you have a lot of tables to audit, write some dynamic SQL that will write hard-coded trigger code and instantiate it just by giving it the table name.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #597853
Posted Friday, November 7, 2008 8:12 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:50 PM
Points: 1,669, Visits: 2,205
Ok , Jeff, what's with the throwing of pork chops ??? Is this some unique cultural reference of yours, or have I simply missed something that society has thrown out there, and in your case, stuck? I'll admit I wouldn't care much to have someone throwing pork chops at me, but if they're edible as opposed to being the functional equivalent of rubber doggie toys, I might be looking to catch a few and have dinner...

Please spill the beans on this, and clear up the mystery for me, won't you? It's got me asking myself "Why pork chops, as opposed to rotten tomatoes or any other rotten vegetable? Or for that matter, why not just the proverbial slap in the face with the white glove, which might actually make a lot more sense in this virtual world of ours?"

Steve
(aka smunson)
:):):)


Steve
(aka sgmunson)

Internet ATM Machine
Post #598979
Posted Friday, November 7, 2008 8:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
They're not so much thrown... as launched. Throwing a pork chop just sounds ridiculous... I mean, who does that, pork chops are delicious... you don't want to just throw them around. LAUNCHING a pork chop on the other hand... that's all kinds of awesome.

Seth Phelabaum
Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #599021
Posted Friday, November 7, 2008 9:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:50 PM
Points: 1,669, Visits: 2,205
Well there, Seth, he did say "high-speed" pork chops, but I interpreted that as relative to the pork chop, as chances are, the fastest you can "launch" such a thing would be the speed associated with a slingshot with some seriously strong and long, but stretchy, rubber. At that point, it's going to hurt someone, and I hadn't really thought of using a slingshot at the time I read the post for precisely that reason... The alternative was to believe he was threatening to hurt someobody...

All that aside, the original query remains: Why Pork Chops? It just conjures up wierd images, like someone defending themselves with a Lacroix stick in order to be able to "return fire", as it were...

Steve
(aka smunson)
:):):)


Steve
(aka sgmunson)

Internet ATM Machine
Post #599073
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse