SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trigger that uses a Parameter with Inserted/Deleted


Trigger that uses a Parameter with Inserted/Deleted

Author
Message
sakieboy
sakieboy
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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
SQLZ
SQLZ
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1698 Visits: 940
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
Garadin
Garadin
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3739 Visits: 4107
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
hodgy
hodgy
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2189 Visits: 596
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

Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36658 Visits: 40272
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!

Matt Miller (4)
Matt Miller (4)
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16031 Visits: 18785
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117343 Visits: 41446
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sgmunson
sgmunson
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9363 Visits: 4424
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)
SmileSmileSmile

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Garadin
Garadin
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3739 Visits: 4107
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
sgmunson
sgmunson
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9363 Visits: 4424
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)
SmileSmileSmile

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search