Return messages from Triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Return messages from Triggers

Author

Topic

kurtgr
Starting Member

25 Posts

Posted - 2009-07-09 : 21:42:05

Hi All

I am doing a delete trigger where i am deleting a record from a table but before i can do this i first check if this record is used in another table. If it does exist I want to return a message to my application stating that record cannot be deleted.

How do I go about doing this

Below is the Code

ALTER TRIGGER [DeleteSkill] ON [dbo].[Skill] INSTEAD OF DELETE
AS

DECLARE @SkillId INT,@StringVariable NVARCHAR(50),@StringVar NVARCHAR(50);

Select @SkillId = SkillId From DELETED

--BEGIN TRANSACTION

RAISERROR(N'Message', 16, 1);

IF EXISTS( SELECT * FROM EmployeeSkill WHERE SkillId = @SkillId )
BEGIN
RAISERROR('Customer has Order History. Delete failed!', 16,1)
ROLLBACK TRANSACTION
END
Else
BEGIN
delete from skill where skillid = @SkillId
RAISERROR('Deleted Successful!', 16,1)
END

--COMMIT TRANSACTION

Thanks In Advance

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-09 : 22:06:00

Duplicate http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129089

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources