sql serverトリガリアルタイム同期データベーステーブルデータ
8586 ワード
sql serverトリガリアルタイム同期データベーステーブルデータ
同じ構造の2つのデータベース・テーブルの作成
CREATE TABLE [dbo].[Table_1](
[id] [varchar](50) NOT NULL,
[name] [varchar](50) NULL,
[dept] [varchar](50) NULL,
[company] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table_2](
[id] [varchar](50) NOT NULL,
[name] [varchar](50) NULL,
[dept] [varchar](50) NULL,
[company] [varchar](50) NULL
) ON [PRIMARY]
GO
トリガーの作成
データベーステーブルTabel 1にフリップフロップを追加し、そのテーブルのデータが変化すると対応するフリップフロップをトリガします.それぞれinsert、update、deleteです.
トリガが既に存在するかどうかを確認
--
if exists(select * from sys.triggers where name= 'trigger_insert')
drop trigger trigger_insert
go
-- insert
create trigger trigger_insert
ON Table_1 for insert
as
insert into Table_2
select * from [Table_1]
where not exists(select 1 from [Table_2]
where [Table_1].id = [Table_2].id)
go
-- update
create trigger trigger_update
ON Table_1 for update
as
if update(id)
begin
update Table_2 set Table_2.name = inserted.name from Table_2,inserted where inserted.id=Table_2.id
end
go
-- delete
create trigger trigger_delete
ON Table_1 for delete
as
begin
delete Table_2 from Table_2,deleted where deleted.id=Table_2.id
end
go
トリガのテスト
データベーステーブルTabel 1にデータを挿入、更新、削除すると、対応するトリガがトリガーされ、Table 2テーブルがリアルタイムで同期更新されます
CREATE TABLE [dbo].[Table_1](
[id] [varchar](50) NOT NULL,
[name] [varchar](50) NULL,
[dept] [varchar](50) NULL,
[company] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table_2](
[id] [varchar](50) NOT NULL,
[name] [varchar](50) NULL,
[dept] [varchar](50) NULL,
[company] [varchar](50) NULL
) ON [PRIMARY]
GO
--
if exists(select * from sys.triggers where name= 'trigger_insert')
drop trigger trigger_insert
go
-- insert
create trigger trigger_insert
ON Table_1 for insert
as
insert into Table_2
select * from [Table_1]
where not exists(select 1 from [Table_2]
where [Table_1].id = [Table_2].id)
go
-- update
create trigger trigger_update
ON Table_1 for update
as
if update(id)
begin
update Table_2 set Table_2.name = inserted.name from Table_2,inserted where inserted.id=Table_2.id
end
go
-- delete
create trigger trigger_delete
ON Table_1 for delete
as
begin
delete Table_2 from Table_2,deleted where deleted.id=Table_2.id
end
go