January 3, 2008 at 2:16 am
Hi,
I need to write Triggers ( Insert, Update and Delete) on a table and insert the values in another table.
Can you give me some help regarding this.
Thanks
Juzer
January 3, 2008 at 3:04 am
try this sample ........
/***********original table********************/
CREATE TABLE employeeData (
emp_id int NOT NULL,
emp_bankAccountNumber char (10) NOT NULL,
emp_salary int NOT NULL,
emp_SSN char (11) NOT NULL,
emp_lname nchar (32) NOT NULL,
emp_fname nchar (32) NOT NULL,
emp_manager int NOT NULL
)
/***********trigger table********************/
CREATE TABLE auditEmployeeData (
audit_log_id uniqueidentifier DEFAULT NEWID(),
audit_log_type char (3) NOT NULL,
audit_emp_id int NOT NULL,
audit_emp_bankAccountNumber char (10) NULL,
audit_emp_salary int NULL,
audit_emp_SSN char (11) NULL,
audit_user sysname DEFAULT SUSER_SNAME(),
audit_changed datetime DEFAULT GETDATE()
)
/****************Trigger*************************/
CREATE TRIGGER updEmployeeData
ON employeeData
FOR INSERT, UPDATE, DELETE
AS
/*Check whether columns 2, 3 or 4 has been updated. If any or all of 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 check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/
IF (COLUMNS_UPDATED() & 14) > 0
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/
BEGIN
-- Audit OLD record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'OLD',
del.emp_id,
del.emp_bankAccountNumber,
del.emp_salary,
del.emp_SSN
FROM deleted del
-- Audit NEW record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'NEW',
ins.emp_id,
ins.emp_bankAccountNumber,
ins.emp_salary,
ins.emp_SSN
FROM inserted ins
END
GO
January 3, 2008 at 4:13 am
Yup. Pretty much. Just check what you need to audit. You don't want to do more than necessary. It shouldn't be necessary to og both the old and the new records on an update, as the old can be got from the last modification (insert or update) Depends on what business requires.
One thing to note is that triggers fire once per statement, not once per row. Whatever code you use in the trigger must be able to handle multiple rows in the inserted/deleted tables (as snk1983's example does)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 6, 2008 at 9:39 am
Hi. I'm trying to understand what happens using the COLUMNS_UPDATED() or UPDATE() functions in a trigger when a multirow update is in process.
Do those functions do the check on the first row of the DELETED/INSERTED tables?
May 6, 2008 at 9:53 am
I believe they are triggered with an update to any row for that column. your code needs to handle which rows get inserted / updated in another table.
May 6, 2008 at 1:09 pm
Hi Steve. Thanks, but this does not really answer my question.
Triggers are supposed to run just once if a single SQL instruction updates several rows in one time. Let's pretend to have a command like
UPDATE tableX SET columnX = columnX * 2
In this situation all the rows of the table get updated.
If I need to run a trigger that checks if a few specific colums are updated, in which case it does some expensive operation, I don't really see how to do it in a perfectly safe way.
Books say, if you cannot solve the situation with aggregate (GROUP BY) calculations on the INSERTED table, you should run a IF @@rowcount=1 check, and abort the trigger execution if the check fails. This is insane: even though normal update operations are on sigle rows, I sometimes need to do mass updates on my table and cannot prohibit them.
The solution I see is to do a check row by row, joining the INSERTED and DELETED tables on the primary key, and comparing the values of the interesting colum row by row. But using cursors inside a trigger is discouraged in the MSDN library.
Actually I am trying to do this inside a CLR managed trigger, where I plan to run a DataReader on the INSERTED and DELETED tables to do this check.
It is amazing how there is no trace of this problem on the MSDN documentation (nor in the internet as far as I have seen!).
May 6, 2008 at 1:34 pm
davidthegray (5/6/2008)
Hi Steve. Thanks, but this does not really answer my question.Triggers are supposed to run just once if a single SQL instruction updates several rows in one time. Let's pretend to have a command like
UPDATE tableX SET columnX = columnX * 2In this situation all the rows of the table get updated.
If I need to run a trigger that checks if a few specific colums are updated, in which case it does some expensive operation, I don't really see how to do it in a perfectly safe way.
Define perfectly safe? In the case above - the trigger would know that ColumnX had been updated. the inserted and deleted columns would have more than one row if the table is sporting more than one row. So - what's "not safe?" You'd know exactly what had been updated.
Books say, if you cannot solve the situation with aggregate (GROUP BY) calculations on the INSERTED table, you should run a
IF @@rowcount=1check, and abort the trigger execution if the check fails. This is insane: even though normal update operations are on sigle rows, I sometimes need to do mass updates on my table and cannot prohibit them.
You'd only need to do that if the code you wrote can't handle more than a single-row update, which would by definition mean you wrote your trigger incorrectly. Assuming that's what they're recommending you do - I'd take that book outside, and set it on fire, becuase it's recommending garbage.
The solution I see is to do a check row by row, joining the INSERTED and DELETED tables on the primary key, and comparing the values of the interesting colum
row by row. But using cursors inside a trigger is discouraged in the MSDN library.
why row by row? you can compare inserted and deleted using set-based operations, so why put a stranglehold on your performance that way?
----------------------------------------------------------------------------------
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?
May 6, 2008 at 3:33 pm
Matt,
Then you should start firing your lighter.
See this sample:
CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATE
AS
DECLARE @rcnt int
SET @rcnt=@@ROWCOUNT
IF @rcnt=0 RETURN
IF @rcnt > 1 BEGIN
RAISERROR('You may only change one item at a time',16,10)
ROLLBACK
RETURN
END
It is taken from here: http://safari.oreilly.com/0201700468/ch08.
I admit my sample was really simplicistic.
I will explain exactly what I want to achieve in my real situation.
I have a table with the full list of articles we handle. I want to build a separate table with a "laundry cleanup" of the codes and the sales names of those articles, which I will use for fast searching the names in a web AJAX project.
I want my trigger to do the laundry work (either through a Transact-SQL trigger calling a CLR function that uses RegEx, or a pure .Net CLR trigger) and to keep the index table updated all the times the names in the articles tables get updated (also deleting the entries from the index table when the Obsolete bit column is set to 1).
99% of the cases this is an easy task. A trigger FOR INSERT,UPDATE can manage this situation with appropriate calls to the IF UPDATE(column) function to avoid waisting precious resources when only other columns of the Articles tables are touched.
But if somebody runs a UPDATE Articles SET Obsolete=1 WHERE ArtCode LIKE 'xxxx%' query, updating several rows in one time, I will have to update my index table for every row that gets touched (there might be rows with a code xxxxy that already had Obsolete=1, which I would possibly want to skip).
To handle this situation, I can only imagine looping through the INSERTED table and comparing the Obsolete value to the value of the row with the same code from the DELETED table. And this is a lot of work!
I envisage that maybe a solution could be something like
IF UPDATE (Obsolete) OR UPDATE (ArtPrimKey) OR UPDATE (NameColumn)
BEGIN
DELETE FROM ArticleIndexTable WHERE ArtPrimKey IN
( SELECT i.ArtPrimKey FROM INSERTED )
INSERT INTO ArticleIndexTable (ArtPrimKey, CleanName)
SELECT i.ArtPrimKey, MyClrFunction(i.NameColumn)
FROM INSERTED i INNER JOIN DELETED d WHERE i.Obsolete=0
END
May 7, 2008 at 10:05 am
davidthegray (5/6/2008)
Matt,Then you should start firing your lighter.
See this sample:
CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATEAS
DECLARE @rcnt int
SET @rcnt=@@ROWCOUNT
IF @rcnt=0 RETURN
IF @rcnt > 1 BEGIN
RAISERROR('You may only change one item at a time',16,10)
ROLLBACK
RETURN
END
It is taken from here: http://safari.oreilly.com/0201700468/ch08.
I admit my sample was really simplicistic.
I will explain exactly what I want to achieve in my real situation.
I have a table with the full list of articles we handle. I want to build a separate table with a "laundry cleanup" of the codes and the sales names of those articles, which I will use for fast searching the names in a web AJAX project.
I want my trigger to do the laundry work (either through a Transact-SQL trigger calling a CLR function that uses RegEx, or a pure .Net CLR trigger) and to keep the index table updated all the times the names in the articles tables get updated (also deleting the entries from the index table when the Obsolete bit column is set to 1).
99% of the cases this is an easy task. A trigger FOR INSERT,UPDATE can manage this situation with appropriate calls to the IF UPDATE(column) function to avoid waisting precious resources when only other columns of the Articles tables are touched.
But if somebody runs a
UPDATE Articles SET Obsolete=1 WHERE ArtCode LIKE 'xxxx%'query, updating several rows in one time, I will have to update my index table for every row that gets touched (there might be rows with a code xxxxy that already had Obsolete=1, which I would possibly want to skip).To handle this situation, I can only imagine looping through the INSERTED table and comparing the Obsolete value to the value of the row with the same code from the DELETED table. And this is a lot of work!
I envisage that maybe a solution could be something like
IF UPDATE (Obsolete) OR UPDATE (ArtPrimKey) OR UPDATE (NameColumn)
BEGIN
DELETE FROM ArticleIndexTable WHERE ArtPrimKey IN
( SELECT i.ArtPrimKey FROM INSERTED )
INSERT INTO ArticleIndexTable (ArtPrimKey, CleanName)
SELECT i.ArtPrimKey, MyClrFunction(i.NameColumn)
FROM INSERTED i INNER JOIN DELETED d WHERE i.Obsolete=0
END
Yup - I'd probably be burning the book (or tearing some page out)....:P That's what I would call a cop-out.
As to your code - I'm not sure I 100% follow your logic, but it's not that far off:
IF UPDATE (Obsolete) OR UPDATE (ArtPrimKey) OR UPDATE (NameColumn)
BEGIN
DELETE FROM ArticleIndexTable WHERE ArtPrimKey IN
( SELECT i.ArtPrimKey FROM INSERTED )
INSERT INTO ArticleIndexTable (ArtPrimKey, CleanName)
SELECT i.ArtPrimKey, MyClrFunction(i.NameColumn)
FROM INSERTED i INNER JOIN DELETED d on i.artprimkey=d.artprimkey
WHERE i.Obsolete=0
END
Even if that's "a lot of work" - just by doing it in a set-based fashion, and avoiding the row-by-row inserts the cursor would do, you should see a rather substantial perf boost. If you actually break the cursor down - you'd find that it's actually doing MORE work, with pulling and then deleting/updating individual rows, and it's doing it in a way that SQL Server can't "help" much.
----------------------------------------------------------------------------------
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?
May 7, 2008 at 10:47 am
Yeah, sure, I forgot the ON part of the join... 😛
I'll give this code a try tomorrow. I still don't like it. I'll probably put an IF @rowcount > 1 clause, so that I'll limit the JOIN operation to the unfrequent cases where several rows get updated at a time, and write a simpler code for the normal operations.
Thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply