触发器，视图

　　1、 触发器

create trigger tr_student
on student--对于哪一个表
instead of delete--替换掉delete语句
as
insert into student values (16,'成龙','男','三班',1003,1103,1113)
go
delete from student where xcode=4

 

--禁用所有触发器
alter table student disable trigger all

 

create trigger sd
on ren
for delete
as
insert into ren values('成龙',78,'男',null,1004)
go
delete from ren where code=4 --不能是主键
select*from ren

 

--执行一条删除数据，用delete表示被删除的那条数据，从中获取

alter trigger t
on score
instead of delete
as
declare @code int
select @code= fcode from deleted
update student set name='刘德华'where xcode=@code
go
delete from score where fcode=15

 

  2、视图

--视图创建

alter view shitu
as
select cangku.ccode,cname,cprice ,cshu,gongying.gname from cangku join gongying on cangku.cgcode=gongying.gcode
go
--调用视图
select*from shitu

 

存储过程实例

 

create table gongying
(
gcode int identity(1001,1) primary key,
gname varchar(50),
gtel varchar(50)
)
go
create table cangku
(
ccode int primary key,
cname varchar(50),
cprice decimal(18,2),
cshu int,
cgcode int
)
go
create table piao
(
pcode int identity(100000001,1),
pname varchar(50),
pprice decimal(18,2),
pshu int,
pzong decimal(18,2)
)
go

insert into gongying values ('海尔','13271501479')
insert into gongying values ('格力','15011701234')
insert into gongying values ('联想','13021096234')
insert into gongying values ('通用','13021981234')
insert into gongying values ('TCL','13021734234')
insert into gongying values ('IBM','13021701256')

insert into cangku values (1,'电冰箱',2100,30,1001)
insert into cangku values (2,'电冰箱',2200,30,1002)
insert into cangku values (3,'笔记本',4100,45,1003)
insert into cangku values (4,'电视机',3290,37,1001)
insert into cangku values (5,'汽车',432100,20,1005)
insert into cangku values (6,'通用电气',43160,54,1004)
insert into cangku values (7,'电脑芯片',800,130,1006)
insert into cangku values (8,'电脑主机',1150,89,1006)
insert into cangku values (9,'空调',2470,65,1002)
insert into cangku values (10,'洗衣机',1598,52,1001)
insert into cangku values (11,'手机',4899,80,1003)
insert into cangku values (12,'充电器',59,49,1003)

--在存储过程中给已建好的表格插入数据
--超市进出货存储过程
alter proc huo
@code int,
@name varchar(50),
@price decimal(18,2),
@shu int,
@gcode int
as
declare @count int
select @count=COUNT(*)from cangku where ccode=@code
if @count=1
begin
if @shu>=0--进货
begin
update cangku set cshu=@shu+cshu where ccode=@code
end
else--出货
begin
declare @cshu int
select @cshu=cshu from cangku where ccode=@code
if @shu+@cshu>=0
begin
print '货物充足'
update cangku set cshu=@shu+cshu where ccode=@code
insert into piao values(@name,@price,abs(@shu),ABS(@price*@shu))
end
else
begin
print '货物不足，及时补充'
end
end
end
else
begin
if @shu>=0
begin
update cangku set cshu=@shu where ccode=@code
end
else
begin
print '没有货物'
end
end
go
exec huo 2,'电冰箱',2200,50,1002
exec huo 2,'电冰箱',2200,-30,1002
exec huo 3,'笔记本',4100,-30,1003

select*from gongying
select*from cangku
select*from piao