表A和表B有相同的记录，创建触发器实现表A更新任何字段的数据后把数据同步更新B表的相同记录

devtools/2024/9/23 7:12:25/

select * from students

select * from students2

–#创建学生表
CREATE TABLE students2 (
stu_num int NOT NULL,
stu_name varchar(50),
stu_gender varchar(10),
stu_age int NOT NULL,
PRIMARY KEY (stu_num)
)

CREATE TABLE students2 (
stu_num int NOT NULL,
stu_name varchar(50),
stu_gender varchar(10),
stu_age int NOT NULL,
PRIMARY KEY (stu_num)
)

–Test sql
insert into students values(1006,‘inserupdate’,‘mail’,20);
delete from students where stu_num=1002;
update students set stu_age=18 where stu_num=1006;

/**** Test Ok completed ***************
– UPDATE

CREATE TRIGGER trg_a_to_b
ON students
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE students2
SET students2.stu_name = inserted.stu_name,
students2.stu_gender = inserted.stu_gender,
students2.stu_age = inserted.stu_age
– 依此类推，为每个需要同步的列添加设置语句
–TableB.LastUpdated = GETDATE() – 假设有一个跟踪最后更新时间的列
FROM inserted
INNER JOIN deleted
ON inserted.stu_num = deleted.stu_num
INNER JOIN students2
ON students2.stu_num = inserted.stu_num
WHERE
– 下面的条件用于检查任何列是否发生了变化
(students2.stu_name <> inserted.stu_name OR
students2.stu_gender <> inserted.stu_gender OR
students2.stu_age <> inserted.stu_age)
END;
GO

USE [Testdb]
GO
/****** Object: Trigger [dbo].[trg_a_to_b] Script Date: 05/10/2024 21:49:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trg_a_to_b]
ON [dbo].[students]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE students2
SET students2.stu_name = inserted.stu_name,
students2.stu_gender = inserted.stu_gender,
students2.stu_age = inserted.stu_age
– 依此类推，为每个需要同步的列添加设置语句
–TableB.LastUpdated = GETDATE() – 假设有一个跟踪最后更新时间的列
FROM inserted
INNER JOIN deleted
ON inserted.stu_num = deleted.stu_num
INNER JOIN students2
ON students2.stu_num = inserted.stu_num
WHERE
– 下面的条件用于检查任何列是否发生了变化
(students2.stu_name <> inserted.stu_name OR
students2.stu_gender <> inserted.stu_gender OR
students2.stu_age <> inserted.stu_age)
END;

– INSERT
USE [Testdb]
GO
/****** Object: Trigger [dbo].[trg_After_InsertUpdate_students] Script Date: 05/10/2024 21:44:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create TRIGGER [dbo].[trg_After_InsertUpdate_students]
ON [dbo].[students]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;

-- 插入操作同步
INSERT INTO students2 (stu_num, stu_name, stu_gender, stu_age)
SELECT i.stu_num, i.stu_name, i.stu_gender, i.stu_age
FROM inserted i
LEFT JOIN deleted d ON i.stu_num = d.stu_num
WHERE d.stu_num IS NULL;

END;

****/