CODEWITHSUNDEEP
sqltriggersinsert
Sarwar Ahmed
Sarwar Ahmed

Reputation: 29

SQL Trigger After Update Insert data into another table

I have two table. One is customer_info and another one is update_log. I want to create a trigger that will execute after update in customer_info. If cust_name, Contact is updated then cust_no, Cust_name, Contact, Date will be added to second table. Second table will have two row with old data and new data. my code is not working. Any help will be highly appreciated.

CREATE  TRIGGER  update_trigger
AFTER UPDATE  ON CUSTOMER_info
FOR EACH ROW
BEGIN
  IF NEW.cust_name <> OLD.cust_name || NEW.contact <> OLD.contact then
    insert into update_log values 
    ( OLD.cust_no,OLD.cust_name, OLD.contact, CURRENT_DATE); 

    insert into update_log  values
    ( NEW.cust_no,NEW.cust_name, NEW.contact,CURRENT_DATE);                 
  END IF
END

Upvotes: 1

Views: 17343

Answers (1)

Muhammet Yasin ARLI
Muhammet Yasin ARLI

Reputation: 564

If You use MSSQL, You can use that trigger:

CREATE  TRIGGER  update_trigger
ON CUSTOMER_info 
FOR update AS

    BEGIN
        IF UPDATE (cust_name) OR UPDATE (contact)
        BEGIN
            INSERT INTO update_log (
                cust_no
                ,Cust_name
                ,Contact
                ,Date
                )
            SELECT cust_no
                ,cust_name
                ,contact
                ,GetDate()
            FROM INSERTED

            INSERT INTO update_log (
            cust_no
            ,Cust_name
            ,Contact
            ,Date
            )
        SELECT cust_no
            ,cust_name
            ,contact
            ,GetDate()
            FROM DELETED
        END
END
GO

Upvotes: 1

Related Questions