sql server 觸發器

標籤：style   io   使用   sp   on   資料   問題   bs   cti   

create TRIGGER [dbo].[tr_CUSTOMER_INFO]
ON [dbo].[T_CUSTOMER_INFO]
AFTER UPDATE
AS
begin

if ((((select bank_number from inserted) <> (select bank_number from deleted))
or (( select mobile_phone from inserted)<>(select mobile_phone from deleted))
or (( select email from inserted)<>(select email from deleted)))
and SUBSTRING((SELECT UPDATE_IP FROM inserted),1,7)<>‘192.168‘)
rollback

end
GO

那麼問題來了：

條件是：我通過uat場測試時；使用路徑為172.21.33.45測試，並將ip存於資料庫表T_CUSTOMER_INFO的UPDATE_IP中。修改使用者資料bank_number時報錯

結果：操作提示錯誤“The transaction ended in the trigger. The batch has been aborted.”

原因：ip非法，造成資料復原。

後來改為：if ((((select bank_number from inserted) <> (select bank_number from deleted))
or (( select mobile_phone from inserted)<>(select mobile_phone from deleted))
or (( select email from inserted)<>(select email from deleted)))
and SUBSTRING((SELECT UPDATE_IP FROM inserted),1,6)<>‘127.21‘)
rollback

正常更新。

註：這樣的觸發器可以防止資料庫用戶端的操作資料庫，區域網路中程式修改資料庫。主要用於生產環境中重要的資料庫表資訊。

sql server 觸發器