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/

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