• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 740
  • Last Modified:

Update trigger to fire only when certain fields are changed.

Dear Experts,

What I'm to do is to fire this trigger only when both RTPNo and StockCode were updated.

This is the code so far I made but this trigger fires every update of any other fields that is not supposed to be...
 
Thanks in advance.

Jimi J.

CREATE TRIGGER Upd_RTP ON dbo.PODetails
AFTER UPDATE

AS


IF not  Update(RTPNo)  And  Not  Update(StockCode)
Return

BEGIN

     IF EXISTS(SELECT 1 FROM inserted)
          IF EXISTS(SELECT 1 FROM deleted)
             
                         
                              ---Update previous dataset
                                Update R Set QtyBal = isnull(R.QtyBal,0) + isNull(D.Quantity,0)
                       FROM RTPDetail AS R Inner JOIN Deleted AS D
                                ON R.RTPid = D.RTPNo And R.StockCode = D.StockCode
                               
                                 INSERT INTO EntryLogs (EDate, EAction, EQuantity)
                                Select GetDate(), 'Old-UPDATED',isnull(Deleted.Quantity,0) from
                                Deleted INNER Join  Inserted  ON Deleted.LineItemID= Inserted .LineItemID

                            ---Update new dataset
                                Update R Set QtyBal = isnull(R.QtyBal,0) - isNull(I.Quantity,0)
                       FROM RTPDetail AS R Inner JOIN Inserted AS I
                                ON R.RTPid = I.RTPNo And R.StockCode = I.StockCode

                                INSERT INTO EntryLogs (EDate, EAction, EQuantity)
                                Select GetDate(), 'New-UPDATED',isnull(Inserted.Quantity,0) from
                                Deleted INNER Join  Inserted  ON Deleted.LineItemID= Inserted .LineItemID
           

 END









0
JaimeJegonia
Asked:
JaimeJegonia
1 Solution
 
Thandava VallepalliCommented:
use  columns_update function  to check first 8 columns are get updated or not...

here is an exmple from books online

 Use COLUMNS_UPDATED
This example creates two tables: an employeeData table and an auditEmployeeData table. The employeeData table, which holds sensitive employee payroll information, can be modified by members of the human resources department. If the employee's social security number (SSN), yearly salary, or bank account number is changed, an audit record is generated and inserted into the auditEmployeeData audit table.

By using the COLUMNS_UPDATED() function, it is possible to test quickly for any changes to these columns that contain sensitive employee information. This use of COLUMNS_UPDATED() only works if you are trying to detect changes to the first 8 columns in the table.

USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME = 'employeeData')
   DROP TABLE employeeData
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME = 'auditEmployeeData')
   DROP TABLE auditEmployeeData
GO
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
   )
GO
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()
   )
GO
CREATE TRIGGER updEmployeeData
ON employeeData
FOR update 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

/*Inserting a new employee does not cause the UPDATE trigger to fire.*/
INSERT INTO employeeData
   VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32)
GO

/*Updating the employee record for employee number 101 to change the salary to 51000 causes the UPDATE trigger to fire and an audit trail to be produced.*/

UPDATE employeeData
   SET emp_salary = 51000
   WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO

/*Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.*/

UPDATE employeeData
   SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
   WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO

F. Use COLUMNS_UPDATED to test more than 8 columns
If you must test for updates that affect columns other than the first 8 columns in a table, you must use the SUBSTRING function to test the proper bit returned by COLUMNS_UPDATED. This example tests for updates that affect columns 3, 5, or 9 in the Northwind.dbo.Customers table.

USE Northwind
DROP TRIGGER  tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
   IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
      + power(2,(5-1)))
      AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
      )
   PRINT 'Columns 3, 5 and 9 updated'
GO

UPDATE Customers
   SET ContactName=ContactName,
      Address=Address,
      Country=Country
GO


itsvtk
0
 
ShogunWadeCommented:
Personally I dont tend to use COLUMNS_UPDATED as i have witnessed cases where it is inaccurate.

I would be more inclined to join the INSERTED AND DELETED tables  

eg:

INSERT MyChangeLog
   SELECT d.*
      FROM INSERTED i
        INNER JOIN DELETED d ON i.ID=d.ID AND i.MyChangedCol!=d.MyChangedCol
0
 
Anthony PerkinsCommented:
Try changing:
AFTER UPDATE

To:
FOR UPDATE
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
JaimeJegoniaAuthor Commented:
Shogun,

Your suggestion gave me excellent idea to save another sleepless night. Thanks so much.

This is the final code, anybody who can refine this is welcome - so far it works in accordance to my expectation.


CREATE TRIGGER Upd_RTP ON dbo.PODetails
FOR UPDATE

AS

 If Exists (SELECT d.*
    FROM INSERTED i
        INNER JOIN DELETED d ON i.LineItemid=d.LineItemid AND i.StockCode!=d.StockCode)  or

 Exists (SELECT d.*
    FROM INSERTED i
        INNER JOIN DELETED d ON i.LineItemid=d.LineItemid AND i.RTPNo!=d.RTPNo)  

BEGIN

     IF EXISTS(SELECT 1 FROM inserted)
          IF EXISTS(SELECT 1 FROM deleted)
                             
                        -- IF (COLUMNS_UPDATED() & 14) > 0   this did not worked
                              ---Update previous dataset
                                Update R Set QtyBal = isnull(R.QtyBal,0) + isNull(D.Quantity,0)
                                  FROM RTPDetail AS R Inner JOIN Deleted AS D
                                ON R.RTPid = D.RTPNo And R.StockCode = D.StockCode
                               
                                 INSERT INTO EntryLogs (EDate, EAction, EQuantity)
                                Select GetDate(), 'Old-UPDATED',isnull(Deleted.Quantity,0) from
                                Deleted INNER Join  Inserted  ON Deleted.LineItemID= Inserted .LineItemID

                            ---Update new dataset
                                Update R Set QtyBal = isnull(R.QtyBal,0) - isNull(I.Quantity,0)
                                   FROM RTPDetail AS R Inner JOIN Inserted AS I
                                ON R.RTPid = I.RTPNo And R.StockCode = I.StockCode

                                INSERT INTO EntryLogs (EDate, EAction, EQuantity)
                                Select GetDate(), 'New-UPDATED',isnull(Inserted.Quantity,0) from
                                Deleted INNER Join  Inserted  ON Deleted.LineItemID= Inserted .LineItemID
       

 END


 
0
 
ShogunWadeCommented:
You are more than welcome.    Glad ive given you inspiration.  
0
 
ShogunWadeCommented:
ps:

i would do the top lines more like this:

Instead of:

If Exists (SELECT d.*
    FROM INSERTED i
        INNER JOIN DELETED d ON i.LineItemid=d.LineItemid AND i.StockCode!=d.StockCode)  or

 Exists (SELECT d.*
    FROM INSERTED i
        INNER JOIN DELETED d ON i.LineItemid=d.LineItemid AND i.RTPNo!=d.RTPNo)  

i would do:

IF EXISTS(SELECT 1 FROM INSERTED i INNER JOIN DELETED d ON  i.LineItemid=d.LineItemid WHERE  i.StockCode!=d.StockCode OR i.RTPNo!=d.RTPNo)


Then this is done in 1 statement which should save some cpu and disk time.



0
 
JaimeJegoniaAuthor Commented:
Shogun,

Wow, it works like like a charm! Thanks once again for you great help. Now I will consider topic closed but i wll posting another related to this - about code optimization. I'am you can help me too.


Jimi J
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now