July 10, 2009 at 8:02 pm
Hello Experts
Below is my Delete Trigger
ALTER TRIGGER [dbo].[DeleteSkill] ON [dbo].[Skill] FOR DELETE
AS
BEGIN
IF EXISTS(SELECT 1
FROM EmployeeSkill
INNER JOIN Deleted ON EmployeeSkill.SkillId = Deleted.SkillId)
BEGIN
RAISERROR('Record already been used in related table(s). Delete failed!', 16,1)
ROLLBACK TRANSACTION
RETURN
END
END
Here is my problem when the condition hold true for the first time if get the message from the RAISERROR.
But say I click the delete button again without changing anything the trigger does not fire.
I am doing the delete from the table true a store procedure
Can you say if my trigger is missing something
Thank in advance
July 11, 2009 at 4:17 am
Hi
I don't see anything missing in your trigger. Maybe you delete the data from EmployeeSkills table?
This shows that your trigger looks correct.
---========================================================
-- Some test table
CREATE TABLE TestTrigger
(
Id INT IDENTITY PRIMARY KEY,
SomeInt INT
)
GO
---========================================================
-- A trigger which avoid deletion of SomeInt "1"
CREATE TRIGGER TR_TestTrigger
ON TestTrigger FOR DELETE
AS
IF EXISTS (SELECT TOP(1) 1 FROM DELETED WHERE SomeInt = 1)
BEGIN
RAISERROR ('Cannot delete 1 from table.', 16, 1)
ROLLBACK TRANSACTION
END
GO
---========================================================
-- Insert two rows into table
INSERT INTO TestTrigger
SELECT 1
UNION ALL SELECT 2
GO
---========================================================
-- Try to delete SomeInt "1" once
DELETE FROM TestTrigger WHERE SomeInt = 1
GO
---========================================================
-- Try to delete SomeInt "1" again
DELETE FROM TestTrigger WHERE SomeInt = 1
GO
---========================================================
-- Show that nothing was deleted
SELECT * FROM TestTrigger
GO
---========================================================
-- Clean up
DROP TRIGGER TR_TestTrigger
DROP TABLE TestTrigger
July 11, 2009 at 9:32 am
Your trigger looks to be doing something that a foreign key could accomplish; any reason to use the trigger at all?
July 11, 2009 at 9:58 am
Cause I would like to use the RAISERROR to display my now message
B
B
July 11, 2009 at 9:58 am
Cause I would like to use the RAISERROR to display my now message
B
B
July 11, 2009 at 10:02 am
If you try to implement your own referential integrity you might run into problems if you miss anything.
Why don't you map system error messages in front-end application?
July 11, 2009 at 10:24 am
The problems is sql server has 7000 different system messages so that would be difficult and also the error number is always 1526(err(1)) so i cannot differentiate when referential integrity occurs.
If you know otherwise would be grateful of your help
Thanks in advance
July 11, 2009 at 10:59 am
I'm quiet sure you don't intend to customize all 7.000 messages 😉
You wrote that you use a procedure to insert your rows. You can use a TRY CATCH block to add your custom error messages:
CREATE TABLE MasterObject
(
Id INT NOT NULL IDENTITY PRIMARY KEY,
SomeInt INT CHECK (SomeInt > 0)
)
CREATE TABLE ChildObject
(
Id INT NOT NULL IDENTITY PRIMARY KEY,
MasterId INT NOT NULL REFERENCES MasterObject(Id),
SomeInt INT
)
GO
CREATE PROCEDURE usp_ChildObject_Insert
@MasterId INT,
@SomeInt INT
AS
BEGIN TRY
INSERT INTO ChildObject (
MasterId,
SomeInt
)
SELECT
@MasterId,
@SomeInt
END TRY
BEGIN CATCH
DECLARE @errNo INT
SELECT @errNo = ERROR_NUMBER();
IF (@errNo = 547)
RAISERROR ('Cannot add child object due to missing master object!', 16, 1)
END CATCH
GO
EXECUTE usp_ChildObject_Insert 1, 2
---- Clean up
--DROP PROCEDURE usp_ChildObject_Insert
--DROP TABLE ChildObject
--DROP TABLE MasterObject
Another approach would be to handle the system errors in front-end.
July 11, 2009 at 11:38 am
Thanks for the help
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply