CREATE TRIGGER [yourtable_insert] ON yourtable FOR INSERT AS insert into table2 select * from inserted
CREATE TRIGGER Trg_Name ON table1 FOR INSERT,DELETE AS Delete table2 From table1 a,table2 b where a.关键字=b.关键字 Insert Into table2 Select * from Inserted GO
CREATE TRIGGER tr_表名_in ON dbo.表名 FOR INSERT AS insert 表2 select * from inserted
两张表都要确定主键,而且主键不要是自增,否则: INSERT INTO MYTABLE SELECT * FROM INSERTED 要出错的。 比如有张表T_A: NAME CHAR(20)(PK) SEX CHAR(1) AGE INT 表T_B结构同上; 对表T_A创建触发器: CREATE TRIG TRIG_T_A ON T_A FOR INSERT,UPDATE,DELETE AS DECLARE @NAME CHAR(20) IF EXISTS(SELECT * FROM DELETED) BEGIN SELECT @NAME=NAME FROM DELTED DELETE FROM T_B WHERE NAME=@NAME END IF EXISTS(SELECT * FROM INSERTED) BEGIN INSERT INTO T_B SELECT * FROM INSERTED END
使用触发器是个好办法。例子: create trigger tri_1 on table1 for insert ,update,delete as delete table2 where 主键=deleted.主键 insert into table2 selete * from inserted go
使用触发器是个好办法。例子: create trigger tri_1 on table1 for insert ,update,delete as set identity insert on insert into table2 selete * from inserted set identity insert off go
大家帮我想想办法, 我的ID是自增的,这样才能产生顺利号码,而且ID也是PK...这样怎么办?
使用触发器 create trigger [tri_insert] on table1 for insert as set identity insert on insert into table2 selete * from inserted set identity insert off go
如果ID不自增就没有PK, 那怎么办?
第一表使用ID自增,第二个表,直接使用第一个表的ID就可以了。这样也可以作为主键的。
假如我想要insert ,update, delete这三个功能都有,那TRIGGER 如何写呢?
另一条路: 你在程序中的数据新增、修改、删除全部用SQL语句来实现 就可以用事务来处理: 例如新增: begin tran insert into tba(...) values(...) insert into tba_bak(...) values(...) commit tran
FOR INSERT
AS
insert into table2 select * from inserted
ON table1
FOR INSERT,DELETE
AS
Delete table2 From table1 a,table2 b where a.关键字=b.关键字
Insert Into table2 Select * from Inserted
GO
CREATE TRIGGER tr_表名_in ON dbo.表名
FOR INSERT
AS
insert 表2 select * from inserted
INSERT INTO MYTABLE SELECT * FROM INSERTED
要出错的。
比如有张表T_A:
NAME CHAR(20)(PK)
SEX CHAR(1)
AGE INT
表T_B结构同上;
对表T_A创建触发器:
CREATE TRIG TRIG_T_A ON T_A
FOR INSERT,UPDATE,DELETE
AS
DECLARE @NAME CHAR(20)
IF EXISTS(SELECT * FROM DELETED)
BEGIN
SELECT @NAME=NAME FROM DELTED
DELETE FROM T_B WHERE NAME=@NAME
END
IF EXISTS(SELECT * FROM INSERTED)
BEGIN
INSERT INTO T_B SELECT * FROM INSERTED
END
SQL修改记录时是先删除旧记录再新增新记录所以一个触发器应该可以实现新增修改删除操作。
create trigger tri_1 on table1 for insert ,update,delete
as
delete table2 where 主键=deleted.主键
insert into table2 selete * from inserted
go
create trigger tri_1 on table1 for insert ,update,delete
as
set identity insert on
insert into table2 selete * from inserted
set identity insert off
go
create trigger [tri_insert] on table1 for insert as
set identity insert on
insert into table2 selete * from inserted
set identity insert off
go
你在程序中的数据新增、修改、删除全部用SQL语句来实现
就可以用事务来处理:
例如新增:
begin tran
insert into tba(...) values(...)
insert into tba_bak(...) values(...)
commit tran