更新触发器以更新另一个表中的记录 [英] update trigger to update records in another table

问题描述

我在User_Table上

I have on User_Table

CREATE TABLE [dbo].[User_TB]
  (
    [User_Id] [varchar](15) NOT NULL,
    [User_FullName] [varchar](50) NULL,
    [User_Address] [varchar](150) NULL,
    [User_Gender] [varchar](10) NULL,
    [User_Joindate] [varchar](50) NULL,
    [User_Email] [varchar](50) NULL,
    [User_Branch] [varchar](50) NULL,
    [User_TeamLeader] [varchar](50) NULL,
    [User_Department] [varchar](50) NULL,
    [User_Position] [varchar](50) NULL,
    [TID] [int] NULL
  )

Break_Table

Break_Table

CREATE TABLE [dbo].[Break_TB]
  (
     [Break_Id] [int] IDENTITY(1,1) NOT NULL,
     [User_Id] [varchar](15) NOT NULL,
     [Date] [date] NULL,
     [Break_Time] [int] NULL,
     [Status] [varchar](50) NULL,
     [Late_time] [int] NULL,
     [TL_Id] [varchar](15) NULL,
     [start_Time] [time](7) NULL,
     [end_Time] [time](7) NULL,
 )

Log_Table

Log_Table

CREATE TABLE [dbo].[Log_TB]
 (
   [User_Id] [varchar](50) NOT NULL,
   [First_Login] [time](0) NULL,
   [Logout] [time](0) NULL,
   [Date] [date] NULL,
   [Working_Hrs] [time](0) NULL,
 )

现在，我要尝试的是User_Table中的User_Id已更新，我想尝试更新另外两个表的User_Id，

Now what am trying to do is that whenever the User_Id from User_Table is Updated , I want trying to update User_Id of Another two tables,

我为此编写了触发器

Alter TRIGGER [dbo].[updateUserId] on [dbo].[User_TB]
 FOR Update
 AS 
 declare @Branch_Name varchar(50),
 @User_Id varchar(15) 

 select @User_Id = i.User_Id from inserted i;

 Update Break_TB set User_Id = @User_Id where User_Id = @User_Id;
 Update Log_TB set User_Id = @User_Id where User_Id = @User_Id;

但是

仅更新Break_TB，不适用于Log_TB

It only updates records from Break_TB, It not works for Log_TB

我不是很擅长触发，如果输入错误，请帮助我。

Am not very good at triggers, if am wrong please Help me.

推荐答案

您需要这样的东西-基于基于集合的解决方案，该解决方案考虑了 UPDATE 语句，您可能一次要更新多行，因此您的触发器还必须处理 Inserted 和<$ c $中的多行c>已删除表。

You would need something like this - a set-based solution that takes into account that in an UPDATE statement, you might be updating multiple rows at once, and therefore your trigger also must deal with multiple rows in the Inserted and Deleted tables.

CREATE TRIGGER [dbo].[updateUserId] 
ON [dbo].[User_TB]
FOR UPDATE
AS 
    -- update the "Break" table - find the rows based on the *old* User_Id
    -- from the "Deleted" pseudo table, and set it to the *new* User_Id
    -- from the "Inserted" pseudo table
    SET User_Id = i.User_Id 
    FROM Inserted i
    INNER JOIN Deleted d ON i.TID = d.TID
    WHERE
        Break_TB.User_Id = d.User_Id

    -- update the "Log" table - find the rows based on the *old* User_Id
    -- from the "Deleted" pseudo table, and set it to the *new* User_Id
    -- from the "Inserted" pseudo table
    UPDATE Break_TB 
    SET User_Id = i.User_Id 
    FROM Inserted i
    INNER JOIN Deleted d ON i.TID = d.TID
    WHERE
        Break_TB.User_Id = d.User_Id

此代码假定： User_TB TID 列c>表是主键，它在更新期间保持不变（以便我可以将 Deleted 伪表中的旧值连接在一起更新后的新值存储在 Inserted 伪表中）

This code assumes that the TID column in the User_TB table is the primary key which remains the same during updates (so that I can join together the "old" values from the Deleted pseudo table with the "new" values after the update, stored in the Inserted pseudo table)

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