快捷導(dǎo)航

sql?server中的觸發(fā)器用法實(shí)例詳解

更新時(shí)間：2024年03月22日 09:49:30 作者：guokeeiron

這篇文章主要給大家介紹了關(guān)于sql?server中觸發(fā)器用法的相關(guān)資料,SQL Server觸發(fā)器是一種特殊類型的存儲(chǔ)過程,它們在數(shù)據(jù)庫中的表上自動(dòng)執(zhí)行,需要的朋友可以參考下

1.觸發(fā)器的定義

觸發(fā)器其實(shí)就是一個(gè)特殊的存儲(chǔ)過程,這個(gè)存儲(chǔ)過程呢,不能調(diào)用罷了, 而是當(dāng)數(shù)據(jù)發(fā)生變化的時(shí)候才觸發(fā)了這個(gè)過程,;

2.觸發(fā)器的分類

1) ,ddl觸發(fā)器, 針對數(shù)據(jù)庫的更新變化

主要是以create,drop,alter開頭的語句的觸發(fā)

2)dml觸發(fā)器這個(gè)針對表達(dá)數(shù)據(jù)更新

after |for 觸發(fā)器(動(dòng)作完成之后觸發(fā))

insert 觸發(fā)器:

delete觸發(fā)器

update觸發(fā)器

3)登錄觸發(fā)器

登錄觸發(fā)器將為響應(yīng) LOGIN 事件而激發(fā)存儲(chǔ)過程。與 SQL Server 實(shí)例建立用戶會(huì)話時(shí)將引發(fā)此事件。登錄觸發(fā)器將在登錄的身份驗(yàn)證階段完成之后且用戶會(huì)話實(shí)際建立之前激發(fā)。因此，來自觸發(fā)器內(nèi)部且通常將到達(dá)用戶的所有消息（例如錯(cuò)誤消息和來自 PRINT 語句的消息）會(huì)傳送到 SQL Server 錯(cuò)誤日志。如果身份驗(yàn)證失敗，將不激發(fā)登錄觸發(fā)器。

3.dml觸發(fā)器的工作原理

在dml觸發(fā)器執(zhí)行過程中, 會(huì)產(chǎn)生兩張臨時(shí)表 inserted and deleted 在執(zhí)行完畢后自動(dòng)釋放;

順序	insert觸發(fā)器	delete觸發(fā)器	update觸發(fā)器
1	執(zhí)行insert語句,在表中插入數(shù)據(jù)	執(zhí)行delete語句,刪除數(shù)據(jù)	執(zhí)行update語句 ,修改數(shù)據(jù)
2	在臨時(shí)表inserted中插入新數(shù)據(jù)的一個(gè)副本	在deleteed表中插入刪除的數(shù)據(jù)的副本	a:首先備份要更新的數(shù)據(jù),插入到deleted表中 b:備份完成后,將更新數(shù)據(jù)插入到inserted 表中
3	觸發(fā)器對數(shù)據(jù)進(jìn)行檢驗(yàn), 確定是否會(huì)滾或者其他操作	觸發(fā)器對刪除數(shù)據(jù)檢查, 來執(zhí)行其他操作	插入新的數(shù)據(jù)

4.觸發(fā)器的應(yīng)用

準(zhǔn)備數(shù)據(jù)

工欲善其事,必先利其器,(準(zhǔn)備數(shù)據(jù)):

create table book (
	bid int primary key  not null ,
	bname varchar(200)  not null , 
	bauther varchar(100)   ,
	bprice decimal(10,2) 
)

insert into book(bid,bname,bprice,bauther)
				values (1,'論語'	,25.6 ,'孔子'),
					   (2,'天龍八部',25.6 ,'金庸') ,
					   (3,'雪山飛狐',32.7 ,'金庸'),
					   (4,'平凡的世',35.8 ,'路遙' ) ,
					   (5,'史記'    ,54.8 ,'司馬遷') ,
					   (6,'狂人日記',35.5  ,'魯迅')

創(chuàng)建觸發(fā)器的格式:

CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]  --給觸發(fā)器文本加密
FOR|after |instead of  [DELETE, INSERT, UPDATE]   ---多加一句after和for 是一個(gè)功能, 用一個(gè)就好了
AS
  T-SQL語句
GO

[WITH ENCRYPTION] --給觸發(fā)器文本加密

for 和after -- 其實(shí)就是同一個(gè)功能,

instead of 就是執(zhí)行某個(gè)操作之前

1.insert觸發(fā)器

創(chuàng)建觸發(fā)器

--判斷數(shù)據(jù)庫中,是否存在這個(gè)觸發(fā)器
IF (object_id('tr_insert_book','tr') is not null) 
    drop trigger tr_insert_book
go
create trigger tr_insert_book
on book
instead of  insert 
as 
begin 
    declare @bid int ;
    select @bid = bid from inserted ;
    insert into books(bid) values(@bid);
    print'插入成功';
end

測試:

insert into book values(8,'鹿鼎記','金庸',60)

結(jié)果:

2.delete觸發(fā)器

創(chuàng)建觸發(fā)器:

IF(OBJECT_ID('TR_DELETE_BOOK','TR') IS NOT NULL ) 
        DROP TRIGGER TR_DELETE_BOOK
GO

CREATE trigger TR_DELETE_BOOK
ON BOOK
FOR DELETE 
AS 
BEGIN 
 
    print'數(shù)據(jù)備份';
    
    if(object_id('book_back','U') is not null ) 
        insert into book_back select * from deleted; 
    else 
        select * into book_back from deleted;
    print '備份完成'
end;

測試:

delete from book where bid =1
select * from book_back

少了bid=1 的數(shù)據(jù)

book_back成功備份;

3.update 觸發(fā)器

創(chuàng)建觸發(fā)器

IF(OBJECT_ID('TR_UPDATE_BOOK','TR') IS NOT NULL ) 
        DROP TRIGGER TR_UPDATE_BOOK
GO

CREATE TRIGGER TR_UPDATE_BOOK
ON BOOK 
AFTER UPDATE
AS 
BEGIN 
    waitfor delay '00:00:02'
    select * from book;
END

測試

update book set bname ='金瓶梅' ,bauter='未知',bprice =12345 where bid =8

結(jié)果:

補(bǔ)充:

update列級更新

需求, 比如book的bid是主鍵 ,那就不能更改, 我們應(yīng)該這么辦呢?

別急,我們可以用update()函數(shù)

IF(OBJECT_ID('TR_UPDATE1_BOOK','TR') IS NOT NULL ) 
        DROP TRIGGER TR_UPDATE1_BOOK
GO

create trigger TR_UPDATE1_BOOK
on book
instead of update
as 
begin 
    if(update(bid))
    begin
        print'主鍵不能更改!!!!';
        rollback; 
    end 
end

測試:

update book set bid =1 
select * from book

DDL觸發(fā)器

創(chuàng)建觸發(fā)器

IF(OBJECT_ID('TR_drop_BOOK','TR') IS NOT NULL ) 
        DROP TRIGGER TR_drop_BOOK
GO
create trigger TR_drop_BOOK
on database 
instead of DROP_TABLE,ALTER_TABLE
as 
begin 
    print'別想著干壞事!!!!好好工作'
    rollback;
end

測試