CODEWITHSUNDEEP
sqlsql-server-2008
Yasmeen
Yasmeen

Reputation: 97

How to create trigger to check if table changed

I'm beginner and i trying to create trigger to check if table changed then i track the changed and insert it into another table

i created table called history and this is my code

create table history
(
ProjectNo INT ,
UserName NVARCHAR(50),
ModifiedDate date,
Budget_Old int,
Budget_New int,
)

and created this trigger 

CREATE TRIGGER t1 on history
on project AFTER UPDATE 
AS BEGIN
   IF UPDATE(Project.Pnumber,Project.Budget)
     INSERT INTO dbo.history (ProjectNo , Username,ModifiedDate,Budget_Old,Budget_New)
          SELECT
             d.ProjectNo, suser_name() ,GETDATE(),d.Budget,i.budget
          FROM 
             Deleted d , inserted i
             where d.projectno = i.projectno
END

i think my if statment is wrong but what i should do to make my query run right to insert this values in history table ? plz help me and sorry for bad English

Upvotes: 2

Views: 2208

Answers (2)

Riyan Azeem
Riyan Azeem

Reputation: 1

ITS WORKS BUDDY ENJOY!!!!!!!!!!!

CREATE TRIGGER tRIGGERNAME1 
 BEFORE UPDATE on history
FOR EACH ROW
BEGIN
 INSERT INTO dbo.history (ProjectNo ,Budget_Old,Budget_New)
VALUES(:OLD.ProjectNo,:OLD.Budget_Old,:NEW.Budget_New);
END;

Upvotes: 0

Hussein Salman
Hussein Salman

Reputation: 8236

Triggers have access to two logical tables that have an identical structure to the table they are defined on which us Project as I assume.

  • INSERTED, which is the new data to go into the table
  • DELETED, which is the old data the is in the table

So You can get the old values in this way:

    CREATE TRIGGER t1
        ON dbo.Project AFTER UPDATE
        AS
            INSERT INTO dbo.history (ProjectNo , Username)
            SELECT d.ProjectNo , d.Username
            FROM DELETED d

If you need to just have one record for specific project in the table history, then you would use inner join based on the projectNo and update the history table accordingly.

Upvotes: 1

Related Questions