We help IT Professionals succeed at work.

SQL studio delete trigger

lantervj
lantervj asked
on
I'm trying to do a simple trigger where any delete to the table will generate an error.

USE [TTdev]
GO
/****** Object:  Trigger [dbo].[sales_center_CRM_companies_Delete]    Script Date: 11/01/2011 15:15:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:&#9;&#9;<Author,,Name>
-- Create date: <Create Date,,>
-- Description:&#9;<Description,,>
-- =============================================
ALTER TRIGGER [dbo].[sales_center_CRM_companies_Delete]
   ON  [dbo].[Sales_Center_CRM_Companies]
   AFTER DELETE
AS
BEGIN
&#9;-- SET NOCOUNT ON added to prevent extra result sets from
&#9;-- interfering with SELECT statements.
&#9;SET NOCOUNT ON;

    RAISERROR ('This is a special error message for Vaughn. Let him know ASAP.',100,1);

END
go


I'm not getting an error when deleting a row using studio.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011

Commented:
don't you want a rollback transaction after the raiseerror?


e.g.

BEGIN
 RAISERROR ('This is a special error message for Vaughn. Let him know ASAP.',100,1);
ROLLBACK TRANSACTION;
RETURN


gave you tried using severity 16?

Author

Commented:
No, I don't care if it is deleted, I just want to know when it happens.

Author

Commented:
Mamagement Studio doesn't seem to be saving my changes.

Commented:
Hi lantervj,

You should use lower severity, not 100, but 16, as Lowfatspread suggested.
If this does not help. check, if trigger is not disabled:
SELECT objectproperty(object_id('sales_center_CRM_companies_Delete'), 'ExecIsTriggerDisabled')

Open in new window

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
Please try the code below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:&#9;&#9;<Author,,Name>
-- Create date: <Create Date,,>
-- Description:&#9;<Description,,>
-- =============================================
create TRIGGER [dbo].[sales_center_CRM_companies_Delete]
   ON  [dbo].[Sales_Center_CRM_Companies]
   AFTER DELETE
AS
if (select count(*) from deleted) > 0
    RAISERROR ('This is a special error message for Vaughn. Let him know ASAP.',16,1);
go

Commented:
If you need to send information message only, you should use low severity level <= 10. Severity level > 10 considered as the error and can rollback the transaction in some cases.

Out of curiosity, why not to use database mail (http://msdn.microsoft.com/en-us/library/ms190307.aspx) instead of raiserror?
CERTIFIED EXPERT

Commented:
If your purpose is to be notified then maybe this will be both more transparent and more reliable: instead of RAISERROR,


declare @msg varchar(1000)

select @msg = 'User ' + suser_sname() + ' on machine ' + host_name() + ' has just deleted company '  +  min(CompanyName) + ' in the table sales_center_CRM_companies' from deleted

exec master..xp_sendmail 'vaughn@mycompany.com',@msg,@no_output=true



Commented:
Just for clarification - unless author is using SQL Server 2000, database mail (sp_senddbmail) is preferred method in compare with xp_sendmail.
CERTIFIED EXPERT
Commented:
continuing... relying on RAISERROR is not very good idea, since it depends on the client software how RAISERROR is processed.

For example:

the way you did it, with errorlevel 100 - you can't even do it, because error levels more than 18 can be only raised by members of sysadmin role, and using WITH LOG option.

If you change errorlevel to 16, and the client if Microsoft Access, the user who tries to delete will receive a message that the record can't be deleted; if he refreshes the records, the record will appear as #DELETED; if he closes and reopens the table, only then the record will be gone.

If you change errorlevel to, say, 5, then there will be no message at all.

This is all because if you have not rolled back the transaction in the trigger, it's totally up to the client software how to interpret the error - from totally ignoring it, to assuming that the attempt to delete has failed.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.