sql trigger print message
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/
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))
and log to it like this:
insert into logs
values ('hello')