在更新触发器之后更新另一个表中的值 [英] 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 GOIf 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屋!
查看全文