Trigger that uses a Parameter with Inserted/Deleted
-
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
-
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.
-
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?
-
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
-
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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? -
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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...:w00t:
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
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.
-
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I would probably like to be in the receiving end of the pork chop launchers :D:D
* Noel -
To make a really long story shorter...
Some manager was complaining about one of his developers as being non-compliant, difficult to control, and generally an arrogant moron. After many suggestions that didn't seem to be taking, I suggested taking the developer out to dinner... and feed him pork chops (bone in)... at point blank range with a slingshot, just to get his attention. 😛
It does get their attention, everytime. 😉
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Interesting... although in cases like that, I'm amazed that anyone thought that a "suggestion" was ever going to do much. Seems to me that simply telling the person "you're acting like an arrogant moron, and if you don't live up to the following list of standards in 30 days, you're going to be fired.", would cleary GET their attention. You then list the standards he/she needs to meet in written form, have them sign a statement acknowledging the problem and agreeing to meet the standards, and see what happens. If he/she fails to sign, he or she is gone, and same goes for he/she continuing to be an arrogant moron. With arrogant morons, it's the only possible solution.
Of course, while this story has no doubt become ancient history by now, it IS an interesting tale. I do wonder though - just what it was that motivated you to be willing to waste something as valuable as pork chops on something so incredibly useless and so lacking in value as an arrogant moron. Attention getter? Yes... Price? A bit high... Entertainment value? That's in the eye of the beholder. The sight of such an absurd event ??? My guess is that from JM's perspective, it was priceless.
Steve
(aka smunson)
:D:D:D
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Absolutely correct on all accounts, Steve... suggestions very similar to yours were made by myself and others but to no avail. I'm amazed at the number of managers have don't know how to mentor, guide, and thin their own flocks.
It seems that my porkchop suggestion has become a metaphor for "take control of your own shop" or "show them who the boss actually is". And, heh, I agree... considering the cost of good meat, I certainly wouldn't waste perfectly good porkchops on someone that couldn't possibly appreciate them... metaphorically or otherwise. 😛
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply