?? 嚴(yán)瓊.sql
字號(hào):
-- 【應(yīng)用案例】圖書館有時(shí)會(huì)對(duì)某圖書的復(fù)本量進(jìn)行更新,如增購某圖書
-- 復(fù)本,或因各種原因?qū)е履硤D書丟失部分復(fù)本。復(fù)本量的更改直接導(dǎo)致庫
-- 存量的改變,但為了不因誤操作破壞數(shù)據(jù)完整性,我們不能對(duì)庫存量直接
-- 修改,可以通過觸發(fā)器,監(jiān)測復(fù)本量的改變,自動(dòng)修改庫存量,這就可以
-- 用到對(duì)選定列的更新進(jìn)行監(jiān)測的方法。
-- =============================================
-- Create trigger basic template(After trigger)
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'book_message'
AND type = 'TR')
DROP TRIGGER book_message
GO
CREATE TRIGGER book_message
ON BOOK FOR UPDATE
AS
IF UPDATE(復(fù)本量)
BEGIN
DECLARE @ISBN ISBN
DECLARE @orignal_FBL int,@orignal_KCL int,
@cur_JYL int, @aim_FBL int
SELECT @orignal_FBL=復(fù)本量,
@orignal_KCL=庫存量,
@ISBN=ISBN
FROM DELETED
SELECT @aim_FBL=復(fù)本量
FROM INSERTED
IF @aim_FBL>=(@orignal_FBL-@orignal_KCL)
BEGIN
UPDATE BOOK
SET 庫存量=@orignal_KCL+(@aim_FBL-@orignal_FBL)
WHERE ISBN=@ISBN
END
ELSE
BEGIN
RAISERROR('對(duì)復(fù)本量的更新違反了用戶定義數(shù)據(jù)完整性',16,10)
ROLLBACK TRANSACTION
END
END
GO
--在XS表上設(shè)計(jì)觸發(fā)器,使得畢業(yè)時(shí)未還完書的學(xué)生不得注銷借書證
--(即:如果學(xué)生借閱量>0,不得刪除該學(xué)生信息)
-- =============================================
-- Create trigger basic template(After trigger)
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'Student_message'
AND type = 'TR')
DROP TRIGGER Student_message
GO
CREATE TRIGGER Student_message
ON XS
FOR DELETE
AS
BEGIN
DECLARE @借書量 int
DECLARE stu_cursor CURSOR
FAST_FORWARD READ_ONLY
FOR
SELECT 借書量
FROM DELETED
OPEN stu_cursor
FETCH NEXT FROM stu_cursor
INTO @借書量
WHILE @@FETCH_STATUS = 0
BEGIN
IF @借書量>0
BEGIN
RAISERROR ('該同學(xué)還存在借閱記錄,不能注銷借書證!', 16, 10)
ROLLBACK TRANSACTION
RETURN
END
FETCH NEXT FROM stu_cursor
INTO @借書量
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
END
GO
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -