gv天堂gv无码男同在线,欧美视频你懂的,毛片一级毛片毛片一级一级毛毛片,亚洲黄色视频免费播放,满18岁免费看的尤物视频,日本欧美三级片免费看,亚洲综合伊人影视在线播放

使用觸發(fā)器TRIGGER
INSERT TRIGGER
觸發(fā)器是由SQL語句集組成的代碼塊,在響應(yīng)某些動(dòng)作時(shí)激活該語句集.一個(gè)觸發(fā)器也可被解釋為特定類型的存儲(chǔ)過程,每當(dāng)動(dòng)作發(fā)生時(shí)執(zhí)行該存儲(chǔ)過程:每當(dāng)基礎(chǔ)表中數(shù)據(jù)受到數(shù)據(jù)操縱語言(DML)語句-INSERT,UPDATE,DELETE的影響時(shí),觸發(fā)器就被激發(fā).
觸發(fā)器的特征:
1. 當(dāng)任何數(shù)據(jù)修改語句發(fā)出時(shí),它被sql server自動(dòng)調(diào)用
2. 在存儲(chǔ)過程的情況下,它不能被顯式地調(diào)用或執(zhí)行
3. 它防止了對數(shù)據(jù)的不正確,未授權(quán)的,和不一致的改變
4. 它不能返回?cái)?shù)據(jù)給用戶
觸發(fā)器語法:
CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
FOR[INSERT | UPDATE | DELETE]
AS sql_statement
幻表:
當(dāng)觸發(fā)器激發(fā)對INSERT,DELETE,或UPDATE語句的響應(yīng)時(shí),兩個(gè)特殊的表被創(chuàng)建:插入表(Inserted)和刪除表(deleted),
示例:
create trigger trgInsertRequisiton
on Requistion
for insert
as
declare @VacnacyReported int
declare @ActualVacancy int
select @ActualVacancy = iBudgetedStrength - currentStrength
from Position join Inserted on
Position.cPositionCode = Inserted.cPositionCode
select @VancyReported = inserted.siNoOfVacancy
from inserted
if(@VancyReported > @ActualVacancy)
begin
print'The actual vacancies are less than the vacncies'
rollback transaction
end
return
DELETE TRIGGER
1.當(dāng)試圖從觸發(fā)器表中刪除一行時(shí),DELETE TRIGGER被觸發(fā)
2.使用DELETE TRIGGER觸發(fā)器來實(shí)現(xiàn)引用完整性約束主要有三種途徑:
1.串聯(lián)方法:每當(dāng)從主表中刪除記錄時(shí),刪除依賴表中的記錄
2.限制方法:如果相尖記錄出現(xiàn)在依賴表中,則限制從主表中刪除記錄
3.無效方法:每當(dāng)一個(gè)記錄從主表中刪除時(shí),把依賴表中所指列的值變?yōu)闊o效
select * from publisher;
create trigger trgfordel
on publisher
for delete
as
begin
print '您刪除的數(shù)據(jù)的內(nèi)容為'
select * from deleted
end
delete from publisher where pub_id = 9;
UPDATE TRIGGER
當(dāng)一個(gè)UPADATE觸發(fā)器被激發(fā)時(shí),它使用關(guān)于其操作的兩個(gè)邏輯表-包含原始行的deleted表和存儲(chǔ)新行的insertd表.
例一:
create trigger trgUpdatePub
on Publisher
for update
as
if update(pub_id)
begin
print 'publisher id cannot be modified'
rollback tran
end
è當(dāng)用戶試圖修改pub_id列時(shí)它就激發(fā),它因此阻止用戶修改pub_id的內(nèi)容并回滾整個(gè)事務(wù)
例二:
create trigger trgUpdateContractRecruiter
on ContractRecruiter
for update
as
declare @AvgPercentageCharge int
select @AvgPercentageCharge = avg(siPercentageCharge)
from ContractRecruiter
if(@AvgPercentageCharge > 11)
begin
print 'The average cannot be more than 11'
rollback transaction
end
修改觸發(fā)器語法:
alter trigger trigger_name
on table_name
[with encryption]
for[insert | delete | update]
as sql_statements
撤消觸發(fā)器:
DROP TRIGGER trigger_name[,...n]
通過觸發(fā)器加強(qiáng)數(shù)據(jù)的完整性
觸發(fā)器可以用來確保和加強(qiáng)業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性,如,只有庫存中有該產(chǎn)品時(shí),才可以出售,如果使用觸發(fā)器,它將會(huì)對不正確的事務(wù)做檢查,并確保只有有效的數(shù)據(jù)才能插入到表中.
例如:某種改變違反了引用完整性,那么所有這樣的改變都被拒絕,因此所有試圖改變數(shù)據(jù)庫中的數(shù)據(jù)都被取消.
CREATE TRIGER trgUpdateDelete
ON TitleAuthor
FOR INSERT,UPDATE
AS
If (SELECT COUNT(*) FROM Titles t JOIN inserted i
ON t.Title_id = i.Title_Id) = 0
BEGIN
PRINT 'Invialid title ID entered'
ROLLBACK
END
IF(SELECT (COUNT(*) FROM Authors t JOIN inserted i
ON t.Au_Id = i.Au_Id) = 0
BEGIN
PRINT 'INVIALID author ID eneter'
ROLLBACK
END
多觸發(fā)器:
SQL SERVER允許在給定表中定義多個(gè)觸發(fā)器.這意味著單個(gè)DML語句可激活兩個(gè)或多個(gè)觸發(fā)器.觸發(fā)器以創(chuàng)建次序被激活.
AFTER 和 INSTEAD OF 觸發(fā)器
AFTER觸發(fā)器在功能上,是在DML操作執(zhí)行成功后.再執(zhí)行的觸發(fā)器.
如:
create triger trgDeletetitles
on Titles
AFTER DELETE
AS
PRINT 'DELETION SUCCESSFUL'
*********************************************************************
假如單個(gè)DML后有多個(gè)AFTER觸發(fā)器,你可以通過使用sp_settriggerorder系統(tǒng)存儲(chǔ)過程來改變這些觸發(fā)器的執(zhí)行次序.
sp_settriggerorder<triggername>,<order-value>,<DML-operation>
ordervalue = FIRST | LAST | NONEè隨機(jī)順序
DML-operation 指出創(chuàng)建觸發(fā)器的DML操作.
sp_settriggerorder 'trgDeleteTitles' , 'FIRST', 'DELETE'
*********************************************************************
INSTEAD OF 觸發(fā)器 替換的觸發(fā)器
如:
create trigger trgPublisherDelete
on publishers
instead of delete
as
print 'Master records cannot be deleted!'
這種觸發(fā)器在一張表上只能創(chuàng)建一個(gè)
不正確之處。。歡迎大家指正