--建立测试环境:
create table bb (id int,name varchar(8))---添加记录
insert bb select 1,'a'
union all select 2,'b'
union all select 3,'c'
union all select 4,'d'select * from bb
--创建触发器create trigger tr1
on bb
instead of insert
as declare @id int
declare @name varchar(800)declare tt cursor for select id,name from inserted
open tt
fetch next from tt into @id,@name
while (@@fetch_status =0)
begin
if exists(select * from bb where id =@id and name=@name)
begin
rollback tran
print'不能输入重复数据,操作被取消'
end
else
begin
insert into bb values(@id,@name)
end
fetch next from tt into @id,@name
end
close tt
deallocate tt
--测试
insert bb select 1,'a'
union all select 5,'d'
create table bb (id int,name varchar(8))---添加记录
insert bb select 1,'a'
union all select 2,'b'
union all select 3,'c'
union all select 4,'d'select * from bb
--创建触发器create trigger tr1
on bb
instead of insert
as declare @id int
declare @name varchar(800)declare tt cursor for select id,name from inserted
open tt
fetch next from tt into @id,@name
while (@@fetch_status =0)
begin
if exists(select * from bb where id =@id and name=@name)
begin
rollback tran
print'不能输入重复数据,操作被取消'
end
else
begin
insert into bb values(@id,@name)
end
fetch next from tt into @id,@name
end
close tt
deallocate tt
--测试
insert bb select 1,'a'
union all select 5,'d'
create table tb
(
ID int identity,
birthday varchar(20),
age int
)
go/*
创建触发器,该触发器在插入数据时根据出生日期计算年龄
*/
create trigger updateage
on tb
for insert
as
update tb set age=datediff(year,convert(datetime,I.birthday),getdate())
from tb join inserted I on I.ID=tb.IDgo--测试
insert tb(birthday)
select '1982-2-22' union select '1980-10-28'--查看结果
select * from tb--删除测试环境
drop table tb--结果
/*
ID birthday age
----------- -------------------- -----------
1 1980-10-28 25
2 1982-2-22 23
*/
Create table author (
au_id Int,
au Nvarchar(20)
)
Create table titleauthor (
id Int,
au_id Int,
title Nvarchar(20)
)
GO
--新增的Trigger
CREATE TRIGGER [author_Insert] ON [dbo].[author]
FOR INSERT
AS
Begin
Insert titleauthor (au_id) values(Select au_id from Insrted )
End
GO
--删除的Trigger
CREATE TRIGGER [author_Delete] ON [dbo].[author]
FOR DELETE
AS
Begin
Delete from titleauthor Where au_id=(Select au_id from Deleted)
End
GO--更新的Trigger
CREATE TRIGGER [author_Update] ON [dbo].[author]
FOR UPDATE
AS
Begin
Update titleauthor Set au_id=(Select au_id from Inserted ) Where au_id=(Select au_id from Deleted )
End
GO
--测试Select * from author
Select * from titleauthorInsert author Values(1,'AA')Select * from author
Select * from titleauthorDelete from author Where au_id=1Select * from author
Select * from titleauthorUpdate author Set au_id=2 Where au_id=1Select * from author
Select * from titleauthor
--商品基本信息表(商品编号,商品名称,库存量,进货日期)
create table jb (id int identity primary key ,name varchar(8),
stock int check(stock>0),date1 datetime )
--商品销售表(编号,销售数量,单价,销售日期)
create table xs (id int foreign key references jb , quantity int,
price money,date2 datetime)--添加记录
insert into jb select 'a',800,'2004-10-1'
union all select 'b',700,'2004-10-2'
union all select 'c',900,'2004-10-3'
union all select 'd',1000,'2004-10-3'
union all select 'e',480,'2004-10-5'--创建触发器,当卖出一种商品时,要求该商品的库存量相应的减少create trigger xs1
on xs
for insert
as
update jb set stock=stock-quantity from jb join inserted on jb.id=inserted.id
select * from jb
select * from xs where id in (select id from inserted)----测试触发器insert into xs values (1,50,150,'2004-10-10')--select * from xs
--select * from jb
insert into xs values (2,150,780,'2004-10-11')