sql server 触发器
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
正常更新。
注：这样的触发器可以防止数据库客户端的操作数据库，局域网中程序修改数据库。主要用于生产环境中重要的数据库表信息。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
