asked on

SQL studio delete trigger

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:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER TRIGGER [dbo].[sales_center_CRM_companies_Delete]
ON [dbo].[Sales_Center_CRM_Companies]
AFTER DELETE
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	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.

Lowfatspread

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?

lantervj

ASKER

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

lantervj

ASKER

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

Rimvis

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

lcohan

Please try the code below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<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

dwkor

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?

Vadim Rapp

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

dwkor

Just for clarification - unless author is using SQL Server 2000, database mail (sp_senddbmail) is preferred method in compare with xp_sendmail.

ASKER CERTIFIED SOLUTION

Vadim Rapp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial