trigger for update\insert

• PJ_SQL

  SSCertifiable

  Points: 6703

  More actions

  March 28, 2017 at 8:28 am

  #328992

  I need to create a trigger when data is inserted or updated in a table In tableA and the audit table should contain information of TableB.

  This is the table trigger needs to be created
  e.g:
  TableA

  ID Dept  PersonID  InsertDate UpdateDate
  1 1022   2    03/24/2016  04/25/2016
  2 1032   3    03/25/2016  04/22/2016

  Table B
  PersonID LastName Firstname
  2    Miller  Joe
  3    Gray   John 

  Trigger needs to be created on TableA for update\insert and it needs to populate data from TableB in the audit table

  How is this achievable?

  Thank you in advance.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  March 28, 2017 at 9:01 am

  #1935287

  PJ_SQL - Tuesday, March 28, 2017 8:28 AM

  I need to create a trigger when data is inserted or updated in a table In tableA and the audit table should contain information of TableB.

  This is the table trigger needs to be created
  e.g:
  TableA

  ID Dept  PersonID  InsertDate UpdateDate
  1 1022   2    03/24/2016  04/25/2016
  2 1032   3    03/25/2016  04/22/2016

  Table B
  PersonID LastName Firstname
  2    Miller  Joe
  3    Gray   John 

  Trigger needs to be created on TableA for update\insert and it needs to populate data from TableB in the audit table

  How is this achievable?

  Thank you in advance.

  How about something like this (you'll have to adapt the table names and fields to match the actuals):
  CREATE TABLE dbo.TABLEA (
    ID int,
    Dept int,
    PersonID int NOT NULL PRIMARY KEY CLUSTERED,
    InsertDate date,
    UpdateDate date
);
INSERT INTO dbo.TABLEA (ID, Dept, PersonID, InsertDate, UpdateDate)
SELECT ID, Dept, PersonID, InsertDate, UpdateDate
FROM (
    VALUES    (1, 1022, 2, '03/24/2016', '04/25/2016'),
            (2, 1032, 3, '03/25/2016', '04/22/2016')
    ) AS X (ID, Dept, PersonID, InsertDate, UpdateDate);
GO

  CREATE TABLE dbo.TABLEB (
    PersonID int NOT NULL PRIMARY KEY CLUSTERED,
    LastName varchar(20),
    Firstname varchar(20)
);
INSERT INTO dbo.TABLEB (PersonID, LastName, Firstname)
SELECT PersonID, LastName, Firstname
FROM (
    VALUES    (2, 'Miller', 'Joe'),
            (3, 'Gray', 'John')
    ) AS X (PersonID, LastName, Firstname);
GO

  CREATE TABLE dbo.AUDITA_WITHB (
    ID int,
    Dept int,
    PersonID int NOT NULL PRIMARY KEY CLUSTERED,
    InsertDate date,
    UpdateDate date,
    LastName varchar(20),
    Firstname varchar(20)
);
GO

  CREATE TRIGGER dbo.TRIG_UPDATE_INSERT_TABLEA ON dbo.TABLEA
    FOR INSERT, UPDATE
AS
BEGIN
INSERT INTO dbo.AUDITA_WITHB (ID, Dept, PersonID, InsertDate, UpdateDate, LastName, Firstname)
SELECT I.ID, I.Dept, I.PersonID, I.InsertDate, I.UpdateDate, B.LastName, B.Firstname
FROM inserted AS I
    INNER JOIN dbo.TABLEB AS B
        ON I.PersonID = B.PersonID
END
GO

  UPDATE TA
SET TA.UpdateDate = GETDATE()
FROM dbo.TABLEA AS TA
WHERE TA.PersonID IN (2,3);

  SELECT *
FROM dbo.AUDITA_WITHB;

  DROP TRIGGER dbo.TRIG_UPDATE_INSERT_TABLEA;
DROP TABLE dbo.TABLEB;
DROP TABLE dbo.TABLEA;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• PJ_SQL

  SSCertifiable

  Points: 6703

  More actions

  March 28, 2017 at 9:27 am

  #1935300

  Thank you.
  Can you please add one more column in AUDITA_WITHB like 'Audit Action' ---Inserted or Updated and also with old and new values?

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  March 28, 2017 at 9:45 am

  #1935307

  PJ_SQL - Tuesday, March 28, 2017 9:27 AM

  Thank you.
  Can you please add one more column in AUDITA_WITHB like 'Audit Action' ---Inserted or Updated and also with old and new values?

  CREATE TABLE dbo.TABLEA (
    ID int,
    Dept int,
    PersonID int NOT NULL PRIMARY KEY CLUSTERED,
    InsertDate date,
    UpdateDate date
);
INSERT INTO dbo.TABLEA (ID, Dept, PersonID, InsertDate, UpdateDate)
SELECT ID, Dept, PersonID, InsertDate, UpdateDate
FROM (
    VALUES    (1, 1022, 2, '03/24/2016', '04/25/2016'),
            (2, 1032, 3, '03/25/2016', '04/22/2016')
    ) AS X (ID, Dept, PersonID, InsertDate, UpdateDate);
GO

  CREATE TABLE dbo.TABLEB (
    PersonID int NOT NULL PRIMARY KEY CLUSTERED,
    LastName varchar(20),
    Firstname varchar(20)
);
INSERT INTO dbo.TABLEB (PersonID, LastName, Firstname)
SELECT PersonID, LastName, Firstname
FROM (
    VALUES    (2, 'Miller', 'Joe'),
            (3, 'Gray', 'John')
    ) AS X (PersonID, LastName, Firstname);
GO

  CREATE TABLE dbo.AUDITA_WITHB (
    AuditAction varchar(20),
    ID int,
    Dept int,
    PersonID int NOT NULL,
    InsertDate date,
    UpdateDate date,
    LastName varchar(20),
    Firstname varchar(20),
    OldID int,
    OldDept int,
    OldPersonID int NOT NULL,
    OldInsertDate date,
    OldUpdateDate date,
    OldLastName varchar(20),
    OldFirstname varchar(20)
);
GO

  CREATE TRIGGER dbo.TRIG_UPDATE_INSERT_TABLEA ON dbo.TABLEA
    FOR INSERT, UPDATE
AS
BEGIN
INSERT INTO dbo.AUDITA_WITHB (AuditAction, ID, Dept, PersonID, InsertDate, UpdateDate, LastName, Firstname,
    OldID, OldDept, OldPersonID, OldInsertDate, OldUpdateDate, OldLastName, OldFirstname)
SELECT CASE WHEN NOT EXISTS (SELECT 1 FROM deleted AS D WHERE D.PersonID = I.PersonID) THEN 'INSERT' ELSE 'UPDATE' END AS AuditAction,
    I.ID, I.Dept, I.PersonID, I.InsertDate, I.UpdateDate, B.LastName, B.Firstname,
    U.OldID, U.OldDept, U.OldPersonID, U.OldInsertDate, U.OldUpdateDate, U.OldLastName, U.OldFirstname
FROM inserted AS I
    INNER JOIN dbo.TABLEB AS B
        ON I.PersonID = B.PersonID
    LEFT OUTER JOIN (
        SELECT D.ID AS OldID, D.Dept AS OldDept, D.PersonID AS OldPersonID, D.InsertDate AS OldInsertDate,
            D.UpdateDate AS OldUpdateDate, TB.LastName AS OldLastName, TB.Firstname AS OldFirstname
        FROM deleted AS D
            INNER JOIN dbo.TABLEB AS TB
                ON D.PersonID = TB.PersonID
        ) AS U
            ON I.PersonID = U.OldPersonID
END
GO

  UPDATE TA
SET TA.UpdateDate = GETDATE()
FROM dbo.TABLEA AS TA
WHERE TA.PersonID IN (2,3);

  SELECT *
FROM dbo.AUDITA_WITHB;

  DROP TRIGGER dbo.TRIG_UPDATE_INSERT_TABLEA;
DROP TABLE dbo.TABLEB;
DROP TABLE dbo.TABLEA;
DROP TABLE dbo.AUDITA_WITHB;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• PJ_SQL

  SSCertifiable

  Points: 6703

  More actions

  March 28, 2017 at 10:24 am

  #1935316

  Thank you for the quick response.

  Here's bit more explanation of the requirement:

  Added SubmitDate to the TABLEA:
  CREATE TABLE dbo.TABLEA (
    ID int,
    Dept int,
    PersonID int NOT NULL PRIMARY KEY CLUSTERED,
    InsertDate date,
    UpdateDate date,
    SubmitDate date
  );
  INSERT INTO dbo.TABLEA (ID, Dept, PersonID, InsertDate, UpdateDate,SubmitDate)
  SELECT ID, Dept, PersonID, InsertDate, UpdateDate,SubmitDate
  FROM (
    VALUES  (3, 1022, 4, '03/24/2016', '04/25/2016','04/26/2016'),
      (2, 1032, 3, '03/25/2016', '04/22/2016','04/25/2016')
    ) AS X (ID, Dept, PersonID, InsertDate, UpdateDate,SubmitDate);
  GO

  CREATE TABLE dbo.TABLEB (
    PersonID int NOT NULL PRIMARY KEY CLUSTERED,
    LastName varchar(20),
    Firstname varchar(20)
  );
  INSERT INTO dbo.TABLEB (PersonID, LastName, Firstname)
  SELECT PersonID, LastName, Firstname
  FROM (
    VALUES  (2, 'Miller', 'Joe'),
      (3, 'Gray', 'John')
    ) AS X (PersonID, LastName, Firstname);
  GO

  Trigger needed:
  •Insert/Update Trigger on TABLEA when SubmitDate value is inserted/updated.
  •Trigger logic will need to query TABLEB for a list of LastName,Firstname associated to the PersonID value from TABLEA.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  March 28, 2017 at 10:41 am

  #1935319

  You've got a data problem.   You changed the PersonID values so that one of them has no match in TABLEB..

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• PJ_SQL

  SSCertifiable

  Points: 6703

  More actions

  March 28, 2017 at 10:53 am

  #1935320

  Here you go:
  CREATE TABLE dbo.TABLEA (
  ID int,
  Dept int,
  PersonID int NOT NULL PRIMARY KEY CLUSTERED,
  InsertDate date,
  UpdateDate date,
  SubmitDate date
  );
  INSERT INTO dbo.TABLEA (ID, Dept, PersonID, InsertDate, UpdateDate,SubmitDate)
  SELECT ID, Dept, PersonID, InsertDate, UpdateDate,SubmitDate
  FROM (
  VALUES (1, 1022, 2, '03/24/2016', '04/25/2016','04/26/2016'),
  (2, 1032, 3, '03/25/2016', '04/22/2016','04/25/2016')
  ) AS X (ID, Dept, PersonID, InsertDate, UpdateDate,SubmitDate);
  GO

  CREATE TABLE dbo.TABLEB (
  PersonID int NOT NULL PRIMARY KEY CLUSTERED,
  LastName varchar(20),
  Firstname varchar(20)
  );
  INSERT INTO dbo.TABLEB (PersonID, LastName, Firstname)
  SELECT PersonID, LastName, Firstname
  FROM (
  VALUES (2, 'Miller', 'Joe'),
  (3, 'Gray', 'John')
  ) AS X (PersonID, LastName, Firstname);
  GO

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  March 28, 2017 at 11:07 am

  #1935326

  Okay...  last one I'm going to do.   You should be able to see what's being done here and start asking questions about what parts you don't understand:
  CREATE TABLE dbo.TABLEA (
    ID int,
    Dept int,
    PersonID int NOT NULL PRIMARY KEY CLUSTERED,
    InsertDate date,
    UpdateDate date,
    SubmitDate date
);
INSERT INTO dbo.TABLEA (ID, Dept, PersonID, InsertDate, UpdateDate, SubmitDate)
SELECT ID, Dept, PersonID, InsertDate, UpdateDate, SubmitDate
FROM (
    VALUES    (1, 1022, 2, '03/24/2016', '04/25/2016','04/26/2016'),
            (2, 1032, 3, '03/25/2016', '04/22/2016','04/25/2016')
    ) AS X (ID, Dept, PersonID, InsertDate, UpdateDate, SubmitDate);
GO

  CREATE TABLE dbo.TABLEB (
    PersonID int NOT NULL PRIMARY KEY CLUSTERED,
    LastName varchar(20),
    Firstname varchar(20)
);
INSERT INTO dbo.TABLEB (PersonID, LastName, Firstname)
SELECT PersonID, LastName, Firstname
FROM (
    VALUES    (1, 'Cool', 'Joe'),
            (2, 'Miller', 'Joe'),
            (3, 'Gray', 'John'),
            (4, 'Fafoofnick', 'Abercrombie')
    ) AS X (PersonID, LastName, Firstname);
GO

  CREATE TABLE dbo.AUDITA_WITHB (
    AuditAction varchar(20),
    AuditInsertDT datetime,
    ID int,
    Dept int,
    PersonID int NOT NULL,
    InsertDate date,
    UpdateDate date,
    SubmitDate date,
    LastName varchar(20),
    Firstname varchar(20),
    OldSubmitDate date
);
GO

  CREATE TRIGGER dbo.TRIG_UPDATE_INSERT_TABLEA ON dbo.TABLEA
    FOR INSERT, UPDATE
AS
BEGIN
INSERT INTO dbo.AUDITA_WITHB (AuditAction, AuditInsertDT, ID, Dept, PersonID, InsertDate, UpdateDate, SubmitDate, LastName, Firstname, OldSubmitDate)
SELECT CASE WHEN NOT EXISTS (SELECT 1 FROM deleted AS D WHERE D.PersonID = I.PersonID) THEN 'INSERT' ELSE 'UPDATE' END AS AuditAction,
    GETDATE() AS AuditInsertDT, I.ID, I.Dept, I.PersonID, I.InsertDate, I.UpdateDate, I.SubmitDate, B.LastName, B.Firstname, D.SubmitDate AS OldSubmitDate
FROM inserted AS I
    INNER JOIN dbo.TABLEB AS B
        ON I.PersonID = B.PersonID
    LEFT OUTER JOIN deleted AS D
            ON I.PersonID = D.PersonID
WHERE I.SubmitDate <> ISNULL(D.SubmitDate, '1799-12-31')
END
GO

  UPDATE TA
SET TA.SubmitDate = DATEADD(day, 0, GETDATE())
FROM dbo.TABLEA AS TA
WHERE TA.PersonID IN (2,3);

  SELECT *
FROM dbo.AUDITA_WITHB;

  UPDATE TA
SET TA.SubmitDate = DATEADD(day, -1, GETDATE())
FROM dbo.TABLEA AS TA
WHERE TA.PersonID = 2;

  SELECT *
FROM dbo.AUDITA_WITHB;

  INSERT INTO dbo.TABLEA (ID, Dept, PersonID, InsertDate, UpdateDate, SubmitDate)
SELECT ID, Dept, PersonID, InsertDate, UpdateDate, SubmitDate
FROM (
    VALUES    (3, 1022, 1, '04/24/2017', '05/25/2016','05/26/2016'),
            (4, 1032, 4, '04/25/2017', '05/22/2016','05/25/2016')
    ) AS X (ID, Dept, PersonID, InsertDate, UpdateDate, SubmitDate);

  SELECT *
FROM dbo.AUDITA_WITHB;

  DROP TRIGGER dbo.TRIG_UPDATE_INSERT_TABLEA;
DROP TABLE dbo.TABLEB;
DROP TABLE dbo.TABLEA;
DROP TABLE dbo.AUDITA_WITHB;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• PJ_SQL

  SSCertifiable

  Points: 6703

  More actions

  March 28, 2017 at 12:19 pm

  #1935359

  Thank you very much,appreciate it.