none
I want to update the status column in onetable when record inserted to another table using triggers RRS feed

  • Question

  •  Table 1 : Employee

    Table 2 : Department.

    Bothe the table contain CompanyID column

    If I insert record in Deparment table , the inserted company ID have to check with the Employee Table Company ID if it is matched then

    Status Column in Employee table should Update to "Arrived". Using sql trigger

    can any body help me out from this.......

    Thursday, August 4, 2011 3:25 AM

Answers

  • hi,

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.Employee (
     Id int NOT NULL IDENTITY PRIMARY KEY,
     Name varchar(10) NOT NULL,
     CompanyId int NOT NULL,
     [Status] varchar(10) NULL
     );
    CREATE TABLE dbo.Department (
     Id int NOT NULL IDENTITY PRIMARY KEY,
     Name varchar(10) NOT NULL,
     CompanyId int NOT NULL
     );
    GO
    CREATE TRIGGER tr_I_Department ON dbo.Department
     FOR INSERT
    AS BEGIN
     IF @@ROWCOUNT = 0 RETURN;
     
     UPDATE dbo.Employee
      SET [Status] = 'Arrived'
      FROM dbo.Employee e
       JOIN inserted i ON i.CompanyId = e.CompanyId;   
    END;
    GO
    -- or, using the MERGE statement,
    /*
    CREATE TRIGGER tr_I_Department ON dbo.Department
     FOR INSERT
    AS BEGIN
     IF @@ROWCOUNT = 0 RETURN;
     
     MERGE dbo.Employee AS Target
     USING (SELECT CompanyId FROM inserted i) AS Source
      ON (Target.CompanyId = Source.CompanyId)
     WHEN MATCHED THEN
      UPDATE SET Target.[Status] = 'Arrived';
    END;
    */
    GO
    INSERT INTO dbo.Employee
     VALUES ( 'Andrea', 1, 'n/a' ), ( 'Kapil', 1, NULL ), ( 'SQLUSA', 2, 'n/a' ),  ( 'Sean', 3, NULL ), ( 'Olaf', 4, NULL );

    INSERT INTO dbo.Department
     VALUES ( 'dep1', 1 );

    INSERT INTO dbo.Department
     VALUES ( 'dep3', 3 );
    GO
    SELECT *
     FROM dbo.Employee;
    GO
    DROP TABLE dbo.Department, dbo.Employee;
    --<----------
    Id          Name       CompanyId   Status
    ----------- ---------- ----------- ----------
    1           Andrea     1           Arrived
    2           Kapil      1           Arrived
    3           SQLUSA     2           n/a
    4           Sean       3           Arrived
    5           Olaf       4           NULL

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/
    • Marked as answer by KJian_ Friday, August 12, 2011 8:43 AM
    Thursday, August 4, 2011 12:40 PM
    Moderator