sql触发器打印消息 [英] sql trigger print message

问题描述

我是在数据库中使用触发器的新手，我创建了一些非常适合我的触发器，除了当我尝试创建一个在删除行后显示消息的触发器时.

I am new at using triggers in database, and I created some triggers that worked very well for me except that when I tried to create a trigger that will show a message after deleting a row.

我尝试使用此代码:

ALTER TRIGGER "mycustomerTrigger" 
AFTER delete ON customers
FOR EACH ROW
BEGIN ATOMIC
print 'trigger is working'
END

当我创建这个触发器时，它不会给出错误消息，但是当我删除一行时，它不会显示我打印的消息.

when I created this trigger it doesn't give error message, but when I delete a row it doesn't show the message that I print.

推荐答案

是因为触发器的运行方式，基本上不在你的查询执行窗口中.一种方法是登录到事件查看器.

It is because of the way that triggers are run, basically it is not in your query execution window. One way of doing this is logging to the event viewer.

Create trigger TestTrigger on
tablefortrigger
for insert
as
–Declare variable to hold message
Declare @Msg varchar(8000)
–Assign to message "Action/Table Name/Time Date/Fields inserted
set @Msg = ‘Inserted | tablefortrigger | ‘ + convert(varchar(20), getdate()) + ‘ | ‘
+(select convert(varchar(5), track)
+ ‘, ‘ + lastname + ‘, ‘ + firstname
from inserted)
–Raise Error to send to Event Viewer
raiserror( 50005, 10, 1, @Msg)

另一种方法是写入文件，这里当然存在权限问题，但您可以这样做:

Another way of doing this is to write to a file, there is of course permissions issues here, but you could do this:

Alter trigger TestTrigger on
tablefortrigger
for insert
as
Declare @Msg varchar(1000)
–Will hold the command to be executed by xp_cmdshell
Declare @CmdString varchar (2000)
set @Msg = ‘Inserted | tablefortrigger | ‘ + convert(varchar(20), getdate()) + ‘ — ‘
+(select convert(varchar(5), track)
+ ‘, ‘ + lastname + ‘, ‘ + firstname
from inserted)
–Raise Error to send to Event Viewer
raiserror( 50005, 10, 1, @Msg)
set @CmdString = ‘echo ‘ + @Msg + ‘ >> C:logtest.log’
–write to text file
exec master.dbo.xp_cmdshell @CmdString

更多信息可以在这里找到:http://www.sql-server-performance.com/2005/log-file-trigger/

More information can be found here: http://www.sql-server-performance.com/2005/log-file-trigger/

这篇关于sql触发器打印消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！