anything wrong with this trigger?

Hello,
I'm trying to create a trigger that will log to the eventviewer whenever
there is an update, delete, or insert statement made to the employee table.
However, I'm UNABLE to run any INSERT or DELETE statement into the employee
table when I have the severity level from 10-16 from the RAISERROR function.
I can run update just fine with the same severity level. Any idea what could
be causing this?
Thx
CREATE TRIGGER dbo.Audit ON dbo.Employee
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @.@.USERNAME varchar(30)
SET @.@.USERNAME = CURRENT_USER
RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
with log
ENDI don't think RAISERROR is the right function for your requirements.
Consider using xp_logevent. More info on this at:
http://msdn.microsoft.com/library/d.../>
sz_6dmc.asp
With RAISERROR, you are unnecessarily raising an error for no apparent
reason. All you wanted was to log that an insert/update/delete occured.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>|||I would also suggest changing the name of your local variable from
@.@.username ( which implies global, but is not) to @.username...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>