Return messages from Triggers to application

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

General SQL Server Forums

New to SQL Server Programming

Return messages from Triggers to application

Author

Topic

kurtgr
Starting Member

25 Posts

Posted - 2009-07-09 : 21:49:44

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-09 : 22:44:46

That is basic referential integrity. It could be handled by a Foreign Key constraint you don't need a trigger.
But regarding your trigger: You should not assume that only one row will be affected by the statement so you shouldn't use a variable. If 10 rows are deleted you will only be checking for one of them.

EDIT:
Your application can CATCH the FK violation error and report a meaningful message to the user.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-10 : 11:34:08

as suggested you can just add a fk relationship from EmployeeSkill to Skill table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources