SQL sql触发器打印消息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29853153/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql trigger print message
提问by user2420263
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.
我是在数据库中使用触发器的新手，我创建了一些非常适合我的触发器，除了当我尝试创建一个在删除行后显示消息的触发器时。
I tried using this code:
我尝试使用此代码：
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.
当我创建此触发器时，它不会给出错误消息，但是当我删除一行时，它不会显示我打印的消息。
回答by BrianAtkins
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
More information can be found here: http://www.sql-server-performance.com/2005/log-file-trigger/
更多信息可以在这里找到：http: //www.sql-server-performance.com/2005/log-file-trigger/
回答by Tobias Feil
If you want to do it in a lightweight way that doesn't require viewing the server log, raising errors or setting special permissions, you can just create a table that holds one column for your logs:
如果您想以不需要查看服务器日志、引发错误或设置特殊权限的轻量级方式执行此操作，您只需创建一个包含一列日志的表：
create table logs (logstring nvarchar(max))
create table logs (logstring nvarchar(max))
and log to it like this:
并像这样登录它：
insert into logsvalues ('hello')
insert into logsvalues ('hello')
