用sql脚本创建sqlserver数据库触发器范例语句

184 阅读 0 评论 122 点赞

我是靠谱客的博主多情信封，最近开发中收集的这篇文章主要介绍用sql脚本创建sqlserver数据库触发器范例语句，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

复制代码代码如下:

 
--会员表 
if object_id('userinfo','u') is not null 
drop table userinfo 
go 
create table userinfo(userid int primary key,user_tegral int,level int) 
insert into userinfo select 1,0,0 
go 
--会员等级表 
if object_id('userlevel','u') is not null 
drop table userlevel 
go 

--插入测试数据 
create table userlevel(level int primary key,mlevel_point decimal(10,2)) 
insert into userlevel select 0,0 
insert into userlevel select 1,100 
insert into userlevel select 2,200 
insert into userlevel select 3,300 
go 



--触发器 
create trigger TR_userInfor 
on userinfo for update 
as 
begin 

update a 
set a.level=b.level 
from userinfo a,userlevel b 
where a.userid in(Select userid from inserted) and a.user_tegral>=b.mlevel_point and 
a.user_tegral<(Select min(mlevel_point) 
from userlevel where mlevel_point>b.mlevel_point) 
end 

--测试 
update userinfo 
set user_tegral=100 
where userid=1 

select * from userinfo 


--另外一个触发器 
set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 
go 
ALTER TRIGGER [AlterName] 
ON [dbo].[FS_User] 
FOR INSERT 
AS 
BEGIN 
SET NOCOUNT ON; 
UPDATE dbo.FS_User SET uname=(SELECT uname FROM Inserted)+'@ml' 
WHERE id IN (SELECT id FROM inserted) 
END 