sql trigger print message

Question

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.

Answer 1

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/

Answer 2

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')