SQL Server Performance Forum – Threads Archive

Need Help for Trigger!

Hi everyone,
I need help to correct this code for insert update trigger. Here what I am trying to do is, if there is any new record inserted into Transaction table, it should insert same record in TransactionLog table as well AND if there is any update(on Transaction) table for this record, it should again insert new record in TransactionLog table. Right now what it is doing is inserting everytime I call insert/update SP even if there are NO changes at all. What condition should I put to insert(to TransactionLog) only if it is inserted new record in Transaction table OR updated with any new values in Transaction table? I think the Insert is correctly as far as I can see but having problem with update. CREATE TRIGGER dbo.MyTrigger
ON dbo.Transactions
FOR INSERT, UPDATE
AS DECLARE @count int SET @count = 0 SELECT @count = count(*) FROM Inserted IF @count > 0
BEGIN
INSERT INTO dbo.TransactionLog(TransactionID, TransactionStatusCode, LogDate, ErrorCode, Comments)
SELECT TransactionID, TransactionStatusCode, GETDATE(), 128, Comments
FROM Inserted
END IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRAN
END
Thanks, Name
———
Dilli Grg (1 row(s) affected)

as per BOL
IF Update() : The IF UPDATE (column_name) clause in the definition of a trigger can be used to determine if an INSERT or UPDATE statement affected a specific column in the table. The clause evaluates to TRUE whenever the column is assigned a value. IF COLUMNS_UPDATED() Alternatively, the IF COLUMNS_UPDATED() clause can be used to check which columns in a table were updated by an INSERT or UPDATE statement. This clause uses an integer bitmask to specify the columns to test. For more information, see CREATE TRIGGER. read more on these, this is one way to solve your problem Madhu

Thanks for your reply Madhu. I tried using IF UPDATE() on columns that will be checked for insert/update, but it doesn’t work for update. What is happening is that even if there are no changes, it is still firing the trigger to insert into history as new record with same old info details. Also, I tried using IF NOT EXISTS for all updatable columns and then fire the update trigger, it is still inserting the new row into TransactionLog table(which already have matching record in Transaction table, so it should not insert/update). No luck so far. [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] <pre id="code"> Thanks, Name ——— Dilli Grg (1 row(s) affected) </pre id="code">

Finally, got it working now. Thanks all. Thanks, Name
———
Dilli Grg (1 row(s) affected)

can u tell us what was the problem? madhu

quote:Originally posted by madhuottapalam can u tell us what was the problem? madhu

What I did was check all updatable fields using inserted and deleted tables before the action. It works as expected. Sample: CREATE TRIGGER dbo.MyTrigger
ON dbo.Transactions
FOR INSERT, UPDATE
AS BEGIN
INSERT INTO dbo.TransactionLog(TransactionID, TransactionStatusCode, LogDate, ErrorCode, Comments)
SELECT i.TransactionID, i.TransactionStatusCode, i.LogDate, i.ErrorCode, i.Comments
FROMINSERTED i
FULL OUTER JOINDELETED d
ONd.TransactionID = i.TransactionID
WHERE( isNULL(i.TransactionStatusCode,0) <> isNULL(d.TransactionStatusCode,0) OR
isNULL(i.LogDate,0) <> isNULL(d.LogDate,0) OR
isNULL(i.ErrorCode,0) <> isNULL(d.ErrorCode,0) OR
isNULL(i.Comments,0) <> isNULL(d.Comments,0) ) OR d.TransactionID IS NULL
END
IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRAN
END Thanks, Name
———
Dilli Grg (1 row(s) affected)

]]>