SqlServer中触发器的使用 .

最新推荐文章于 2024-09-14 22:25:22 发布

haoshilu123

最新推荐文章于 2024-09-14 22:25:22 发布

阅读量363

点赞数 1

分类专栏： sqlserver 文章标签： sqlserver login insert delete 测试 date

本文链接：https://blog.csdn.net/haoshilu123/article/details/6927334

版权

sqlserver 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

--案例表
USE stuDB
GO
CREATE TABLE stuInfo
(
stuName varchar(20) not null ,
stuNo char(6) PRIMARY KEY,
stuAge int not null check(stuAge>0 and stuAge<100),
stuID varchar(18) not null,
stuSeat int identity(1,1),
stuAddress varchar(50) default '住址不详'
)
GO
insert into stuInfo(stuName,stuNo,stuAge,stuId)
select '1','010001',21,'421990198909112311' union
select '2','010002',22,'421990198909111342' union
select '3','010003',23,'421990198909111242' union
select '4','010004',21,'421990198909111278' union
select '5','010005',22,'421990198909114556' union
select '6','010006',23,'421990198909117845' union
select '7','010007',24,'421990198909112345' union
select '8','010008',20,'421990198909117457' union
select '9','010009',22,'421990198909111557' union
select '蒋雯丽','010010',20,'421990198909111905'
go
CREATE TABLE stuMarks
(
ExamNo CHAR(7) primary key,
stuNo CHAR(6) NOT NULL references stuInfo(stuNo),
writtenExam INT NOT NULL,
LabExam INT NOT NULL
)
GO
insert into stuMarks
select '09001','010001',58,68 union
select '09002','010002',66,77 union
select '09003','010003',86,45 union
select '09004','010004',62,62 union
select '09005','010005',67,54 union
select '09006','010006',78,69 union
select '09007','010007',60,83 union
select '09008','010008',48,74 union
select '09009','010009',54,69 union
select '09010','010010',61,55
--创建登录触发器
--限制sa用户只能登陆3次
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'sa' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'sa') > 3
ROLLBACK;
END;
use BOOK
go
create trigger create_trigger
on database
for create_table
as
print '正在创建表'
go
--禁止用户删除和修改表
use BOOK
go
create trigger deny_drop_table
on database
for drop_table,alter_table
as
begin
print '不允许删除和修改表'
rollback tran
end
--测试触发器
alter table test add tname varchar(20) not null
--禁用DDL触发器
disable trigger 触发器名 on all server
disable trigger 触发器名 on database
--启用DDL触发器
enable trigger 触发器名 on all server
enable trigger 触发器名 on database
--删除DDL触发器
drop trigger 触发器名 on all server
drop trigger 触发器名 on database
--after insert触发器
select * from stuinfo
select * from stumarks
--限制用户插入年龄》30 或《18的信息
--删除触发器
drop trigger check_insert_stuinfo
create trigger check_insert_stuinfo
on stuinfo
for insert
as
begin
declare @age int
--获取当前用户插入的数据
select @age=stuage from inserted
--判断年龄信息
if @age>30 or @age<18
begin
raiserror('年龄数据必须在18-30之间',16,1)
rollback tran
end
end
--测试触发器
insert into stuinfo(stuname,stuno,stuage,stuid,stuaddress)
values('CCC','010014',22,'555666',default)
SELECT * FROM STUINFO
--After delete触发器
--禁止用户删除信息
create trigger deny_delete_stuinfo
on stuinfo
for delete
as
begin
declare @name varchar(20)
select @name=stuname from deleted
if @name='李斯文' or @name='梅超风'
begin
raiserror('不允许删除指定的学员信息',16,1)
rollback tran
end
end
--备份删除的信息
--创建备份表
select * into StuBak from stuinfo where 1=2
--删除列stuseat
alter table stubak drop column stuseat
--添加列stuseat
alter table stubak add stuseat int
--创建触发器
create trigger delete_bak_stuinfo
on stuinfo
for delete
as
insert into stubak(stuname,stuno,stuage,stuid,stuseat,stuaddress) select * from deleted
--测试
delete from stuinfo where stuage>30
select * from stubak
--After update触发器
create trigger update_stumarks
on stumarks
for update
as
begin
--如何判断有没有更新writtenEXAM和labExam
if update(writtenExam) or update(labExam)
begin
raiserror('成绩字段不能为更新',16,1)
rollback tran
end
end
--测试触发器
update stumarks set labexam=labexam+10
--日志审计
create table tb_log
(
log_id int identity(1,1) primary key,
username varchar(20) not null,
log_date datetime,
log_desc varchar(100)
)
create trigger log_trigger
on stuinfo
for insert,delete,update
as
--获取当前登录用户
declare @name varchar(20)
set @name=ORIGINAL_LOGIN()
--获取当前操作时间
declare @date datetime
set @date=getdate()
declare @desc varchar(100)
if exists(select * from inserted) and not exists(select * from deleted)
set @desc='插入数据'
else if(exists(select * from deleted) and not exists(select * from inserted))
set @desc='删除数据'
else
set @desc='修改数据'
insert into tb_log values(@name,@date,@desc)
go
insert into StuInfo(stuname,stuno,stuage,stuid)
values('AAA','001',21,'123456')
select * from tb_log
--instead of触发器
create table stu
(
sid int,
sname varchar(20)
)
create table computer
(
sid int,
marks float
)
insert into stu values(1,'AAA')
insert into stu values(2,'BBB')
insert into stu values(3,'CCC')
insert into computer values(1,'60')
insert into computer values(2,'70')
insert into computer values(3,'80')
select * from stu
select * from computer
--创建视图
create view view_stu_computer
as
select stu.sid,sname,marks from stu,computer
where stu.sid=computer.sid
go
--查询视图
--视图基于一张表创建，可以对视图实施增、删、改操作
--视图基于多张表创建，不允许对视图实施。。。（在视图上创建instead of触发器）
select * from view_stu_computer
insert into view_stu_computer values(4,'DDD',90)
delete from view_stu_computer where sid=4
create trigger insert_view_stu_computer
on view_stu_computer
instead of insert
as
--从inserted表中获取插入的数据
declare @id int,@name varchar(20),@marks float
select @id=sid,@name=sname,@marks=marks from inserted
--向基表中插入数据
insert into stu values(@id,@name)
insert into computer values(@id,@marks)
go
create trigger delete_view_stu_computer
on view_stu_computer
instead of delete
as
--从deleted表中获取正在删除的编号
declare @id int
select @id=sid from deleted
--从基表删除数据
delete from computer where sid=@id
delete from stu where sid=@id
go