使用触发器: create trigger t_1 on 表a for insert as insert into 表1 (d) select m from inserted insert into 表2 (d) select m from inserted ...... insert into 表7 (d) select m from inserted gocreate trigger t_2 on 表a for delete as delete 表1 where d = deleted.m delete 表2 where d = deleted.m ...... delete 表7 where d = deleted.m go
用触发器 同步插入 CREATE TRIGGER trigger_name_insert ON tablename FOR INSERT as insert into b1 .... . . . insert into b1 ....同步删除 CREATE TRIGGER trigger_name_del ON tablename FOR DELETE ASdelete from b1 .... . . . delete from b1 ....
create trigger insert_test on yourtable form insert as begin update 要更新的表 set 字段=yourtable.field from insertedend go
@in_Fieldn varchar(50), @out_RetCode int output --返回值:0表示操作失败,1表示操作成功 ) AS SET NOCOUNT ON if @in_model=1 如果参数值为1,表示插入数据 IF EXISTS(SELECT Name FROM TableName WHERE TableName.Name=@in_Field01) BEGIN SELECT @out_RetCode=0 --判断是否存在同名 RETURN END ELSE BEGIN TRAN INSERT INTO 表a( m --关联字段 Field01, Field02, Field03, Field04, Fieldn ) Values ( @in_m @in_Field01, @in_Field02, @in_Field03, @in_Field04, @in_Fieldn ) IF @@ERROR <> 0 BEGIN ROLLBACK TRAN Return -1 END INSERT INTO 表其他1( d, Field01, Field02, Field03, Field04, Fieldn
) Values ( @in_m @in_Field01, @in_Field02, @in_Field03, @in_Field04, @in_Fieldn ) IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN -1 END --这里可按照以上方式添加若干的表 INSERT INTO 表其他7( d, Field01, Field02, Field03, Field04, Fieldn
) Values ( @in_m @in_Field01, @in_Field02, @in_Field03, @in_Field04, @in_Fieldn ) IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN -1 END SELECT @out_RetCode=1 --输出1表示录入成功 -------------------------- if @in_model=2 如果参数值为2,表示删除数据 --这里写删除语句 --------------------------- COMMIT TRAN RETURN 0 SET NOCOUNT OFF ----------------------------- ----------------------------- 以上代码是在SQL SERVER7.0里写的。希望能够帮助你 。
多谢各位了,我觉得分数太少,对不住大家。今天终于把这个trigger写出来了,这也是我平生第一个trigger:)CREATE TRIGGER dbo.tri_AddNode ON dbo.MDL_RELIABLE for INSERT AS select child as c from inserted --更新dictionary表 insert into dictionary select child, 1, '' from inserted insert into dictionary select child, 2, '' from inserted insert into dictionary select child, 3, '' from inserted insert into dictionary select child, 4, '' from inserted --更新suggestion表 insert into suggestion select child, '' from inserted --更新index_raw_type表 insert into index_raw_type select child, '', 0, 1, '', '', '', '' from inserted --更新data_index表 DECLARE cur_AddNode CURSOR FOR select system_id, child, 100, 0, 0, 0, 1 from system_info, mdl_reliable OPEN cur_AddNode DECLARE @vSystem_id int declare @vIndex_id int declare @vIntg_index float declare @vVector_1 float declare @vVector_2 float declare @vVector_3 float declare @vVector_4 float FETCH NEXT FROM tnames_cursor INTO @vSystem_id, @vIndex_id, @vIntg_index, @vVector_1, @vVector_2, @vVector_3, @vVector_4WHILE (@@FETCH_STATUS <> -1) BEGIn insert into data_index values(@vSystem_id, @vIndex_id, @vIntg_index, @vVector_1, @vVector_2, @vVector_3, @vVector_4) endCLOSE cur_AddNode DEALLOCATE cur_AddNode
create trigger t_1 on 表a for insert
as
insert into 表1 (d) select m from inserted
insert into 表2 (d) select m from inserted
......
insert into 表7 (d) select m from inserted
gocreate trigger t_2 on 表a for delete
as
delete 表1 where d = deleted.m
delete 表2 where d = deleted.m
......
delete 表7 where d = deleted.m
go
同步插入
CREATE TRIGGER trigger_name_insert
ON tablename
FOR INSERT
as
insert into b1 ....
.
.
.
insert into b1 ....同步删除
CREATE TRIGGER trigger_name_del
ON tablename
FOR DELETE
ASdelete from b1 ....
.
.
.
delete from b1 ....
as
begin
update 要更新的表 set 字段=yourtable.field
from insertedend
go
(
@in_model varchar(50), 1表示插入;2表示删除
@in_m char(8),
@in_Field01 varchar(15),
@in_Field02 varchar(6),
@in_Field03 varchar(50),
@in_Field04 varchar(50),
...
@in_Fieldn varchar(50),
@out_RetCode int output --返回值:0表示操作失败,1表示操作成功
)
AS
SET NOCOUNT ON
if @in_model=1 如果参数值为1,表示插入数据
IF EXISTS(SELECT Name FROM TableName
WHERE TableName.Name=@in_Field01)
BEGIN
SELECT @out_RetCode=0 --判断是否存在同名
RETURN
END
ELSE
BEGIN TRAN
INSERT INTO 表a(
m --关联字段
Field01,
Field02,
Field03,
Field04,
Fieldn
)
Values
(
@in_m
@in_Field01,
@in_Field02,
@in_Field03,
@in_Field04,
@in_Fieldn
)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
Return -1
END
INSERT INTO 表其他1(
d,
Field01,
Field02,
Field03,
Field04,
Fieldn
)
Values
(
@in_m
@in_Field01,
@in_Field02,
@in_Field03,
@in_Field04,
@in_Fieldn
) IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN -1
END
--这里可按照以上方式添加若干的表
INSERT INTO 表其他7(
d,
Field01,
Field02,
Field03,
Field04,
Fieldn
)
Values
(
@in_m
@in_Field01,
@in_Field02,
@in_Field03,
@in_Field04,
@in_Fieldn
)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN -1
END
SELECT @out_RetCode=1 --输出1表示录入成功
--------------------------
if @in_model=2 如果参数值为2,表示删除数据
--这里写删除语句
---------------------------
COMMIT TRAN
RETURN 0
SET NOCOUNT OFF
-----------------------------
-----------------------------
以上代码是在SQL SERVER7.0里写的。希望能够帮助你 。
ON dbo.MDL_RELIABLE
for INSERT
AS
select child as c from inserted
--更新dictionary表
insert into dictionary select child, 1, '' from inserted
insert into dictionary select child, 2, '' from inserted
insert into dictionary select child, 3, '' from inserted
insert into dictionary select child, 4, '' from inserted
--更新suggestion表
insert into suggestion select child, '' from inserted
--更新index_raw_type表
insert into index_raw_type select child, '', 0, 1, '', '', '', '' from inserted
--更新data_index表
DECLARE cur_AddNode CURSOR
FOR
select system_id, child, 100, 0, 0, 0, 1
from system_info, mdl_reliable
OPEN cur_AddNode
DECLARE @vSystem_id int
declare @vIndex_id int
declare @vIntg_index float
declare @vVector_1 float
declare @vVector_2 float
declare @vVector_3 float
declare @vVector_4 float
FETCH NEXT FROM tnames_cursor INTO @vSystem_id, @vIndex_id, @vIntg_index, @vVector_1, @vVector_2, @vVector_3, @vVector_4WHILE (@@FETCH_STATUS <> -1)
BEGIn
insert into data_index values(@vSystem_id, @vIndex_id, @vIntg_index, @vVector_1, @vVector_2, @vVector_3, @vVector_4)
endCLOSE cur_AddNode
DEALLOCATE cur_AddNode