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