March 11, 2009 at 7:26 am
I have a table (SQL Server 2005) in which i get new add and updates from SSIS package. I have applied triggers for add update and delete (3 independent triggers) to keep logging of updates. I want to fire trigger only when other fields except few fields are changed. how can i do this?
Suppose I have 10 fields, out of which three are updated for all records due to SSIS package (and i do not want to log that change in logging table) but if other 7 fields are updated only in that case trigger may run and save changes in logging table. we have to give report to client that how much change is coming on daily bases or whenever SSIS packag runs? But only for 7 fields, but triggers runs for all 10 fields, which is not acceptable. there are 500,000 rows that SSIS update on every run, so its not acceptable, only if change is placded in required 7 fields than it may log, so that we can send report to clients.
Shamshad Ali.
March 11, 2009 at 7:51 am
There are a few options.
You could have the SSIS package disable the trigger when it starts and enable it when it finishes. That's only a good option if the SSIS package runs when no other data is being modified, so it would also need to lock the whole table while it's running.
You could use the Update() function in the trigger to detect which columns are being updated, and only run the trigger if one of the ones you want to log is being updated.
You could have the trigger detect which login is doing the update, and set up a unique login just for the SSIS package. If it detects that login, it just returns without logging anything.
Might be more, those are the ones I can think of off the top of my head.
I'd probably use the login one, since that will involve the least locks and least processing power.
create trigger MyTable_MyTrigger on dbo.MyTable
after update
as
if (select user) = 'SSISLogin'
return;
else
begin
...do logging...
end
Something like that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2009 at 9:05 am
you can do it by comparing the COLUMNS_UPDATED function to see if specific columns have changed, and only let the trigger run if that is true; it uses a bitmask based on the columns....
here is a complete example using an address table, where if the name changes, i don't care, but if specific columns of the address change, it logs the changes to an audit table.
This might steer you in the direction you want to go:
[font="Courier New"]
#T=TRIGGER COLUMNS_UPDATED example
CREATE TABLE MYADDRESSES(
EMPNO INT IDENTITY(1,1) PRIMARY KEY,
ENAME VARCHAR(100),
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 32 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[/font]
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply