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 触发器