使用触发器:
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
go
create 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 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_4
WHILE (@@FETCH_STATUS <> -1)
BEGIn
insert into data_index values(@vSystem_id, @vIndex_id, @vIntg_index, @vVector_1, @vVector_2,
@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