復制代碼 代碼如下:
--會員表
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
--插入測試數(shù)據(jù)
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
--觸發(fā)器
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
--另外一個觸發(fā)器
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
您可能感興趣的文章:- SQL Server 觸發(fā)器 表的特定字段更新時,觸發(fā)Update觸發(fā)器
- sqlserver 禁用觸發(fā)器和啟用觸發(fā)器的語句
- SQLServer觸發(fā)器創(chuàng)建、刪除、修改、查看示例代碼
- SQL Server觸發(fā)器及觸發(fā)器中的事務學習
- 在登錄觸發(fā)器錯誤情況下連接SQL Server的方法
- sqlserver 觸發(fā)器教程
- sqlserver 觸發(fā)器實例代碼
- sqlserver中觸發(fā)器+游標操作實現(xiàn)
- Sql Server觸發(fā)器的使用
- SQL SERVER 觸發(fā)器介紹
- SQL SERVER中各類觸發(fā)器的完整語法及參數(shù)說明
- SQLSever中的觸發(fā)器基本語法與作用