求一个简单的触发器的写法?,增加a表的同时,增加b和c,要求3个表id相同,且a.softname=b.softname;a.sname=c.sname 。
各位可以用下面的sql继续写。谢谢各位大侠。IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
IF OBJECT_ID('c') IS NOT NULL
DROP TABLE cGOCREATE TABLE A( id int,sname varchar(8), softname varchar(16))
go
insert A SELECT
01 , 'aaa','aaaaa' UNION ALL SELECT
02 , 'bbb' ,'bbbbb'
CREATE TABLE B( id int,巡检时间 datetime,softname varchar(16))
go
insert B SELECT
01 , '2009-8-1','aaaaa' UNION ALL SELECT
02 , '2009-8-3','bbbbb' CREATE TABLE c( id int,开始时间 datetime,结束时间 datetime,sname varchar(8))
go
insert c SELECT
01 , '2009-8-1' , '2009-8-3','aaa' UNION ALL SELECT
02 , '2009-8-20' , '2009-8-23' ,'bbb'select * from a
select * from b
select * from c
各位可以用下面的sql继续写。谢谢各位大侠。IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
IF OBJECT_ID('c') IS NOT NULL
DROP TABLE cGOCREATE TABLE A( id int,sname varchar(8), softname varchar(16))
go
insert A SELECT
01 , 'aaa','aaaaa' UNION ALL SELECT
02 , 'bbb' ,'bbbbb'
CREATE TABLE B( id int,巡检时间 datetime,softname varchar(16))
go
insert B SELECT
01 , '2009-8-1','aaaaa' UNION ALL SELECT
02 , '2009-8-3','bbbbb' CREATE TABLE c( id int,开始时间 datetime,结束时间 datetime,sname varchar(8))
go
insert c SELECT
01 , '2009-8-1' , '2009-8-3','aaa' UNION ALL SELECT
02 , '2009-8-20' , '2009-8-23' ,'bbb'select * from a
select * from b
select * from c
on a
for insert
asinsert b (id,巡检时间,softname )
select id,getdate(),softname
from inserted i
where not exists (
select 1 from b
where id=i.id and softname=i.softname
)insert c (id,开始时间,结束时间,softname )
select id,getdate(),dateadd(day,3,getdate()),sname
from inserted i
where not exists (
select 1 from c
where id=i.id and sname=i.sname
)
go时间的逻辑你没说明,我乱猜写的
on a
for insert
as
FOR INSERT
AS
BEGIN
INSERT B (ID) SELECT ID FROM INSERTED I WHERE NOT EXISTS(SELECT 1 FROM B WHERE B.ID=I.ID AND B.SOFTNAME=I.SOFTNAME)INSERT C (ID) SELECT ID FROM INSERTED I WHERE NOT EXISTS(SELECT 1 FROM C WHERE C.ID=I.ID AND C.SOFTNAME=I.SOFTNAME)END
各位可以用下面的sql继续写。谢谢各位大侠。 IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
IF OBJECT_ID('c') IS NOT NULL
DROP TABLE c GO CREATE TABLE A( id int,sname varchar(8), softname varchar(16))
go
insert A SELECT
01 , 'aaa','aaaaa' UNION ALL SELECT
02 , 'bbb' ,'bbbbb'
CREATE TABLE B( id int,巡检时间 datetime,softname varchar(16))
go
insert B SELECT
01 , '2009-8-1','aaaaa' UNION ALL SELECT
02 , '2009-8-3','bbbbb'
CREATE TABLE c( id int,开始时间 datetime,结束时间 datetime,sname varchar(8))
go
insert c SELECT
01 , '2009-8-1' , '2009-8-3','aaa' UNION ALL SELECT
02 , '2009-8-20' , '2009-8-23' ,'bbb' select * from a
select * from b
select * from c
create trigger TB_TR
on a
for insert
asinsert b (id,巡检时间,softname )
select id,convert(varchar(10),getdate(),120),softname
from inserted A
where not exists(
select 1 from B
where id=A.id-1 and softname=A.softname
)insert c (id,开始时间,结束时间,SNAME )
select id,getdate(),convert(varchar(10),getdate(),120),sname
from inserted D
where not exists (
select 1 from c
where id=D.id-1 and sname=D.sname
)
goINSERT A SELECT '03' , 'ccc' ,'dddd'
INSERT A SELECT '04' , 'dddd' ,'eeeee'
select * from a
select * from b
select * from c /*a
id sname softname
1 aaa aaaaa
2 bbb bbbbb
3 ccc dddd
4 dddd eeeeeb
id 巡检时间 softname
1 2009-08-01 00:00:00.000 aaaaa
2 2009-08-03 00:00:00.000 bbbbb
3 2009-08-13 00:00:00.000 dddd
4 2009-08-13 00:00:00.000 eeeee
c
开始时间 结束时间 sname
2009-08-01 00:00:00.000 2009-08-03 00:00:00.000 aaa
2009-08-20 00:00:00.000 2009-08-23 00:00:00.000 bbb
2009-08-13 09:24:46.327 2009-08-13 00:00:00.000 ccc
2009-08-13 09:24:46.327 2009-08-13 00:00:00.000 dddd
*/
如果吸要日期部分,在建表时类型用varchar(10)
如下
create trigger TB_TR
on a
for insert
asinsert b (id,巡检时间,softname )
select id,getdate(),softname
from inserted A
where not exists(
select 1 from B
where id=A.id-1 and softname=A.softname
)insert c (id,开始时间,结束时间,SNAME )
select id,getdate(),getdate(),sname
from inserted D
where not exists (
select 1 from c
where id=D.id-1 and sname=D.sname
)
go
如果用INSERT INTO的写法应该怎么写呢?
学生表关联班级表,班级表关联系部表.
题目要求是:写个插入和删除学生的触发器.
插入学生的触发器:当插入学生时。插入学生所在的班级的人数加1,同时该班级所在的系部的系部人数也加1.
删除学生的触发器:当删除学生时。删除学生的所在的班级的人数减1.同时该班级所在的系部的系部人数也减1。
--系部表
create table Department
(
DepID varchar(10) primary key,--系部编号
DepName varchar(50),--系部名称
DepManager varchar(50),--系主任
DepCount int--系部人数
)
insert into Department(DepID,DepName,DepManager,DepCount)values('1001','软件技术系','汪卫星',4000)
insert into Department(DepID,DepName,DepManager,DepCount)values('1002','网络技术系','五国军',3000)
insert into Department(DepID,DepName,DepManager,DepCount)values('1003','数字艺术系','爱因斯坦',2000)
insert into Department(DepID,DepName,DepManager,DepCount)values('1004','电子商务系','刘德华',1000)--班级表
create table Class
(
ClaID varchar(10) primary key,--班级编号
ClaName varchar(50),--班级姓名
ClaManager varchar(50),--辅导员
ClaCount int,--班级人数
DepID varchar(10) foreign key references Department(DepID)--所在系部
)
insert into Class(ClaID,ClaName,ClaManager,ClaCount,DepID)values('2001','.NET1班','王力1',51,1001)
insert into Class(ClaID,ClaName,ClaManager,ClaCount,DepID)values('2002','.NET2班','王力2',52,1001)
insert into Class(ClaID,ClaName,ClaManager,ClaCount,DepID)values('2003','.NET3班','王力3',53,1001)--学生表
create table Student
(
StuID int primary key,--学生学号
StuName varchar(50),--学生姓名
StuSex bit,--学生性别
StuBir datetime,--学生生日
StuAdd varchar(50),--学生地址
ClaID varchar(10) foreign key references Class(ClaID)--所在班级
)
insert into Student(StuID,StuName,StuSex,StuBir,StuAdd,ClaID)values(1,'张辰1',1,'1989/7/23','重庆1',2001)
insert into Student(StuID,StuName,StuSex,StuBir,StuAdd,ClaID)values(2,'张辰2',0,'1989/7/23','重庆2',2002)
insert into Student(StuID,StuName,StuSex,StuBir,StuAdd,ClaID)values(3,'张辰3',1,'1989/7/23','重庆3',2003)
CREATE TRIGGER TRI_INSERT ON Student
FOR INSERT
AS
UPDATE Class SET ClaCount=ClaCount+1 FROM Class,INSERTED WHERE Class.ClaID=INSERTED.ClaID
UPDATE DepCount SET DepCount=DepCount+1 FROM Class,INSERTED ,Department
WHERE Class.ClaID=INSERTED.ClaID AND CLASS.DepID=Department.DepID--2
CREATE TRIGGER TRI_DEL ON Student
FOR DELETE
AS
UPDATE Class SET ClaCount=ClaCount-1 FROM Class,DELETED WHERE Class.ClaID=DELETED.ClaID
UPDATE DepCount SET DepCount=DepCount-1 FROM Class,DELETED ,Department
WHERE Class.ClaID=DELETED.ClaID AND CLASS.DepID=Department.DepID
或者create trigger tg on student
for delete,insert
as
begin
if exists(select 1 from deleted)
begin
update a set a.DepCount = a.DepCount-1 from Department a inner join Class b on a.DepID = b.DepID inner join deleted c on b.ClaID = c.ClaID
update a set a.ClaCount = a.ClaCount-1 from Class a,deleted b where a.ClaID = b.ClaID
end
if exists(select 1 from inserted)
begin
update a set a.DepCount = a.DepCount+1 from Department a inner join Class b on a.DepID = b.DepID inner join inserted c on b.ClaID = c.ClaID update a set a.ClaCount = a.ClaCount+1 from Class a,inserted b where a.ClaID = b.ClaID
end
endgoinsert into Student(StuID,StuName,StuSex,StuBir,StuAdd,ClaID)values(4,'张辰u',1,'1989/7/23','重庆1',2001)select * from department
select * from class
drop table Student
drop table Class
drop table Department