--建触发器
create trigger tr_process_dbuse1 on dbo.dbuse1
for insert,delete,update
as
if @@rowcount=0 return
delete a from dbo.dbuse2 a
where exists(
select * from deleted
where useName=a.useName
and usePassword=a.usePassword
and email=a.email)
--若是使dbuse2保持与dbuse1数据一致,那就要改结构了:
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dbuse1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE dbuse1 (id int identity(1,1),useName varchar(20),usePassword varchar(10),email varchar(20))
go
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dbuse2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE dbuse2 (id int,useName varchar(20),usePassword varchar(10),email varchar(20))
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_1]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_1]
GO
CREATE TRIGGER [tr_1] ON dbo.dbuse1
FOR insert,update,delete
AS
if not exists(select * from inserted)
delete from dbuse2 where exists(select * from deleted where id=dbuse2.id)--删除
else
if not exists(select * from deleted)
insert into dbuse2 select * from inserted--插入
else
update dbuse2 set useName=i.useName,usePassword=i.usePassword,email=i.email from inserted i,deleted d where i.id=d.id and i.id=dbuse2.id
go
if not exists (select * from sysdatabases where name='数据库')
create database 数据库
go
2.建表及触发器
use 数据库
go
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dbuse1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE dbuse1 (useName varchar(20),usePassword varchar(10),email varchar(20))
go
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dbuse2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE dbuse2 (useName varchar(20),usePassword varchar(10),email varchar(20))
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_1]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_1]
GO
CREATE TRIGGER [tr_1] ON dbo.dbuse1
FOR insert,update,delete
AS
--你给的条件不足,无法写
go
CREATE TRIGGER TRIG_TabXY
ON dbuse1
AFTER INSERT
AS
IF exists(SELECT top 1 * FROM inserted)
BEGIN
INSERT INTO dbuse2(dept_no,person_no,sex,salary)
SELECT dept_no,person_no,sex,salary FROM inserted
PRINT 'dbuse1有記錄增加了,同時觸發dbuse2。'
--建触发器
create trigger tr_process_dbuse1 on dbo.dbuse1
for insert,delete,update
as
if @@rowcount=0 return
delete a from dbo.dbuse2 a
where exists(
select * from deleted
where useName=a.useName
and usePassword=a.usePassword
and email=a.email)