请教同一个服务器,两个数据库之间同步更新数据的触发器SQL语句

发布网友 发布时间：2022-05-01 04:34

我来回答

共3个回答

懂视网 时间：2022-05-01 08:56

A.[序号], A.[日期], A.[IP], A.[部门], A.[操作员], A.[事件] from A

where not exists(select 1 from B where A.[序号]=B.[序号]);

 

　　2、将A表中不存在于B表的记录，插入到B表中。

INSERT INTO B([序号], [日期], [IP], [部门], [操作员], [事件])
 SELECT [序号], [日期], [IP], [部门], [操作员], [事件]
 FROM A 
WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.[序号]=B.[序号]);

 

     3、将B表中的记录一行一行更新到A表中。

UPDATE A SET A.[日期] = B.[日期], A.[IP] = B.[IP], A.[部门] = B.[部门], A.[操作员] = B.[操作员], A.[事件] = B.[事件] 
FROM A INNER JOIN B ON A.[序号]=B.[序号];

 

MySql:

UPDATE his_pet A, his_pet B SET A.Name = B.Name, A.NamePy = B.NamePy WHERE A.MID = B.MID

 

数据库同步相关的SQL语句

标签：

热心网友 时间：2022-05-01 06:04

一个例子：
create table t_a(id int identity(1,1),username varchar(20))

create table t_b(id int identity(1,1),username varchar(20))

create trigger tr_a on t_a
for update,insert,delete
as
alter table t_b DISABLE TRIGGER tr_b
----------------------------------
if not exists (select * from deleted) --插入
insert t_b(username) select username from inserted
else if not exists (select * from inserted)--删除
delete t_b where id in (select id from deleted)
else--更新
update a set a.username=i.username from t_b as a,inserted as i where a.id=i.id
alter table t_b ENABLE TRIGGER tr_b

------------------------------------------
create trigger tr_b on t_b
for update,insert,delete
as
alter table t_a DISABLE TRIGGER tr_a
----------------------------------
if not exists (select * from deleted) --插入
insert t_a(username) select username from inserted
else if not exists (select * from inserted) --删除
delete t_a where id in (select id from deleted)
else --更新
update a set a.username=i.username from t_a as a,inserted as i where a.id=i.id
alter table t_a ENABLE TRIGGER tr_a

select * from t_a
select * from t_b

--测试：
insert t_a(username) values('test')
insert t_b(username) values('test2')
update t_a set username='test1' where id=1
update t_b set username='test3' where id=1
delete from t_a where id=1
delete from t_b where id=2
--------------------------
select * from t_a
select * from t_b

热心网友 时间：2022-05-01 07:22

CREATE TRIGGER 触发器名称 ON [dbo].[表名]
FOR INSERT, UPDATE, DELETE
AS
begin
insert 数据库.dbo.表名(列名) select scend from inserted
end