locked
Update a value in another table by after insert trigger RRS feed

  • Question

  • Hi

    suppose I have this two tables:

    Holidays table:

    CREATE TABLE [dbo].[Holidays](
        [Id] [int] NOT NULL,
        [EmpId] [int] NOT NULL,
        [SDate] [datetime2](7) NULL,
        [EDate] [datetime2](7) NULL,
        [ReturnStatus] [bit] NULL)

    Employees table:

    CREATE TABLE [dbo].[Employees](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[EmpName] [nvarchar](50) NOT NULL,
    	[DeptId] [int] NULL,
    	[StaffId] [int] NULL)

    I want to update the StaffId value in Employees table to the value '2' after insert of Holidays table where the new Holidays.EmpId of inserted record = Employees.Id

    I tried this:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[TRG_HolidaysAftIns]
    ON [dbo].[Holidays]
    AFTER INSERT AS
    BEGIN
    UPDATE Employees set StaffId = 2
    FROM inserted
    WHERE EmployeeId = inserted.id
    END

    but nothing happened why? and how to solve?


    Monday, April 15, 2019 8:20 AM

Answers

  • Hi ,

     

    Would you like following result? I will bold the part I modified, and please try it.

    CREATE TABLE [dbo].[Holidays](
    [Id] [int] NOT NULL,
    [EmpId] [int] NOT NULL,
    [SDate] [datetime2](7) NULL,
    [EDate] [datetime2](7) NULL,
    [ReturnStatus] [bit] NULL)
    CREATE TABLE [dbo].[Employees](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [nvarchar](50) NOT NULL,
    [DeptId] [int] NULL,
    [StaffId] [int] NULL)
    insert into [Employees]([EmpName],[DeptId],[StaffId]) values ('Sun',1,1)
    go
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [dbo].[TRG_HolidaysAftIns]
    ON [dbo].[Holidays]
    AFTER INSERT AS
    BEGIN
    UPDATE t 
    set StaffId = 2
    FROM [Employees] t
    WHERE exists (select 1 from inserted a where a.EmpId=t.Id)
    end 
    go 
    
    insert into [Holidays] values(1,1,'20180212','20160203',1)
    
    select* from [Holidays]
    /*
    Id          EmpId       SDate                       EDate                       ReturnStatus
    ----------- ----------- --------------------------- --------------------------- ------------
    1           1           2018-02-12 00:00:00.0000000 2016-02-03 00:00:00.0000000 1
    */
    select* from [Employees]
    /*
    Id          EmpName                                            DeptId      StaffId
    ----------- -------------------------------------------------- ----------- -----------
    1           Sun                                                1           2
    */


    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 15, 2019 9:52 AM

All replies

  • Hi ,

     

    Would you like following result? I will bold the part I modified, and please try it.

    CREATE TABLE [dbo].[Holidays](
    [Id] [int] NOT NULL,
    [EmpId] [int] NOT NULL,
    [SDate] [datetime2](7) NULL,
    [EDate] [datetime2](7) NULL,
    [ReturnStatus] [bit] NULL)
    CREATE TABLE [dbo].[Employees](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [nvarchar](50) NOT NULL,
    [DeptId] [int] NULL,
    [StaffId] [int] NULL)
    insert into [Employees]([EmpName],[DeptId],[StaffId]) values ('Sun',1,1)
    go
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [dbo].[TRG_HolidaysAftIns]
    ON [dbo].[Holidays]
    AFTER INSERT AS
    BEGIN
    UPDATE t 
    set StaffId = 2
    FROM [Employees] t
    WHERE exists (select 1 from inserted a where a.EmpId=t.Id)
    end 
    go 
    
    insert into [Holidays] values(1,1,'20180212','20160203',1)
    
    select* from [Holidays]
    /*
    Id          EmpId       SDate                       EDate                       ReturnStatus
    ----------- ----------- --------------------------- --------------------------- ------------
    1           1           2018-02-12 00:00:00.0000000 2016-02-03 00:00:00.0000000 1
    */
    select* from [Employees]
    /*
    Id          EmpName                                            DeptId      StaffId
    ----------- -------------------------------------------------- ----------- -----------
    1           Sun                                                1           2
    */


    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 15, 2019 9:52 AM
  • Thanks

    It is working now well!

    Monday, April 15, 2019 10:26 AM
  • This one will work as well:

    CREATE TRIGGER [dbo].[TRG_HolidaysAftIns]
    ON [dbo].[Holidays]
    AFTER INSERT AS
    BEGIN
    
    Merge [Employees] t 
    Using inserted a on a.EmpId=t.Id
    when matched then
    UPDATE  
    Set StaffId = 2;
    
    end 
    go 

    Monday, April 15, 2019 2:48 PM