--查看資料庫中所有觸發器 https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15
-- 涂聚文(Geovin Du) edit https://www.mssqltips.com/sqlservertip/5909/sql-server-trigger-example/
select * from sysobjects where xtype='TR'
--
exec sp_helptext 'TriClerkOfficeInsert'
--創建insert插入型別觸發器 PositionRoleDefaut ,當添加工員資料，在角色表中的添加
if (object_id('TriClerkOfficeInsert', 'tr') is not null)
drop trigger TriClerkOfficeInsert
go
create trigger TriClerkOfficeInsert
on ClerkOffice -- 指定創建觸發器的表
for insert --插入觸發
as
--定義變數
declare @id uniqueidentifier, @DefaultRoleId int, @PositionId int;
--在inserted表中查詢已經插入記錄資訊
select @id = ClerkId, @PositionId = ClerkPosition from inserted;
select @DefaultRoleId= RoleDefautSet from PositionRoleDefaut where RolePositionId=@PositionId
insert into ClerkOfficeRole(ClerkRoleKey,ClerkRoleSet) values(@id,@DefaultRoleId)
print '添加成功！';
go
--修改時觸發器
if (object_id('TriClerkOfficeUpdate', 'tr') is not null)
drop trigger TriClerkOfficeUpdate
go
create trigger TriClerkOfficeUpdate
on ClerkOffice -- 指定創建觸發器的表
for update --修改時觸發
as
--定義變數
declare @id uniqueidentifier, @DefaultRoleId int, @PositionId int,@ClerkName nvarchar(100),@OldClerkName nvarchar(100);
--更新前的資料
--select @id = ClerkId,@OldClerkName=ClerkName,@PositionId=ClerkPosition from deleted; -- 修改前的資料就存在 deleted 這個表中
--if (exists (select * from ClerkOffice where ClerkName like '%'+ @OldClerkName + '%'))
-- begin
--更新后的資料
select @id = ClerkId,@ClerkName=ClerkName,@PositionId=ClerkPosition from inserted;-- 修改后的資料就存在 inserted 這個表中
--end
--select @id = ClerkId, @PositionId = ClerkPosition from ClerkOffice;
select @DefaultRoleId= RoleDefautSet from PositionRoleDefaut where RolePositionId=@PositionId;
if(exists (select * from ClerkOfficeRole where ClerkRoleKey=@id))
begin
update ClerkOfficeRole set ClerkRoleSet=@DefaultRoleId where ClerkRoleKey=@id;
end
else
begin
insert into ClerkOfficeRole(ClerkRoleKey,ClerkRoleSet) values(@id,@DefaultRoleId);
end
print '修改成功！';
go
--delete洗掉型別觸發器
if (object_id('TriClerkOfficeDelete', 'TR') is not null)
drop trigger TriClerkOfficeDelete
go
create trigger TriClerkOfficeDelete
on ClerkOffice
for delete --洗掉觸發
as
declare @id uniqueidentifier
select @id=ClerkId from deleted;
delete ClerkOfficeRole where ClerkRoleKey=@id;
print '洗掉資料成功！';
go
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/1201.html
標籤：SQL Server
下一篇：sql 排序去重的方法