在更新触发器之后更新另一个表中的值 [英] Update a value in another table by after update trigger

问题描述

嗨

假设我有这两个表：

假期表：

 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）

我想在将RetuenStatus更新为值'1'后将StaffId的值更新为值'1'

我试过这个但是我有语法错误：

 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 
 CREATE TRIGGER TRG_HolidayAftUpd 
 ON假日
更新后
 AS 
 BEGIN 
设定NOCOUNT ON; 
如果UPDATE（ReturnStatus） - 和ReturnStatus = 1 
 BEGIN 
更新t 
 SET StaffId = 1 
来自员工t 
 WHERE存在（选择1从插入a a.EmpId = t.Id）
 END 
 END 
 GO 

如果我取消了对"And ReturnStatus = 1"的评论。它给了我语法错误。

如何解决？

解决方案

 

您想要下面的结果吗？我会大胆修改我修改过的部分，请尝试一下。

 

 ---- drop table假期删除表Employees 
 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）
插入[Employees]（[EmpName]，[DeptId]，[StaffId] ]）值（'Sun'，2,2）
插入[Holidays]值（1,1，'20180212'，'20160203'，3）
插入[Holidays]值（2， 1，'20180212'，'20160203'，4）
 go 
 
 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 CREATE TRIGGER TRG_HolidayAftUpd 
 ON  [假期]  
更新后
 AS 
 BEGIN 
设置NOCOUNT ON; 
  IF存在（从插入中选择1，其中ReturnStatus = 1） 
 BEGIN 
 UPDATE t 
 SET StaffId = 1 
 FROM  [员工]  t 
 WHERE存在（从插入a中选择1，其中a.EmpId = t.Id）
 END 
 END 
 GO 
 
 update [Holidays] set ReturnStatus = 1 
 
 select * from [Employees] 
 / * 
 Id EmpName DeptId StaffId 
 -------- --- ----------------------------------------------- --- ----------- ----------- 
 1太阳1 1 
 * / 
 
 
 

希望它可以帮到你。

 

最好的问候，

Rachel

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 value of StaffId to the value '1' after the update of RetuenStatus to the value '1'

I tried this but I have syntax error:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER TRG_HolidayAftUpd
   ON  Holiday
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	IF UPDATE(ReturnStatus)-- AND ReturnStatus = 1
	BEGIN
		UPDATE t
		SET StaffId = 1
		FROM Employee t
		WHERE exists (select 1 from inserted a where a.EmpId=t.Id)	
	END
END
GO

If I cancelled the comment on "And ReturnStatus = 1" it gives me a syntax error.

How to solve please?

解决方案

Hi ,

 

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

 

----drop table Holidays  drop table Employees
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',2,2)
insert into [Holidays] values(1,1,'20180212','20160203',3)
insert into [Holidays] values(2,1,'20180212','20160203',4)
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER TRG_HolidayAftUpd
   ON  [Holidays]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	IF exists (select 1 from inserted where ReturnStatus = 1)
	BEGIN
		UPDATE t
		SET StaffId = 1
		FROM [Employees] t
		WHERE exists (select 1 from inserted a where a.EmpId=t.Id)	
	END
END
GO

update [Holidays] set ReturnStatus=1

select* from [Employees]
/*
Id          EmpName                                            DeptId      StaffId
----------- -------------------------------------------------- ----------- -----------
1           Sun                                                1           1
*/

Hope it can help you.

 

Best Regards,

Rachel

这篇关于在更新触发器之后更新另一个表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！