August 4, 2010 at 6:23 pm
CREATE TRIGGER t_del_emp ON [dbo].[empmas]
FOR DELETE
AS
declare @empid varchar(10)
SELECT @empid=d.empid FROM DELETED d
declare @empdetid INT
IF EXISTS(SELECT * FROM empdet WHERE empid=@empid AND deactivated is null)
BEGIN
SET @empdetid = (SELECT empdetid FROM empdet WHERE empid=@empid AND deactivated is null)
UPDATE empdet SET deactivated=getdate() WHERE empdetid=@empdetid
END
BEGIN TRANSACTION
if EXISTS (SELECT * FROM empdet WHERE empid=@empid)
BEGIN
RAISERROR 14001 'You cannot delete this Employee '
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
i want to update on other table if record has a relation on another table which field is deactivated is null or if is not null it will rollback..
if deactivated is
August 4, 2010 at 8:10 pm
While I'm not going to argue with Joe, I don't think that DRI can handle this particular situation.
I would agree that I don't really understand why you would have a situation where a master row (empmas) would be deleted, but detail rows (empdet) would be kept and marked as deactivated. I would think you'd want to mark the master row as deactivated and use that in reporting/selects to exclude rows. But given the apparent design I have these comments about the trigger:
1. Your current trigger only handles situations where one row is deleted. If there is a set-based delete you will only enforce the business logic for one row.
2. By default a trigger is part of the outer transaction and the ROLLBACK will rollback the outer transaction as well. I think this is the logic you want.
See the article, Introduction to DML Triggers[/url] for a more thorough explanation.
I think I'd make this trigger an INSTEAD OF trigger and here is how I'd do it with a short example:
IF EXISTS (SELECT 1 FROM sys.tables AS T WHERE T.name = N'empmas' AND T.schema_id = SCHEMA_ID('dbo'))
BEGIN
DROP TABLE dbo.empmas;
END
Go
IF EXISTS (SELECT 1 FROM sys.tables AS T WHERE T.name = N'empdet' AND T.schema_id = SCHEMA_ID('dbo'))
BEGIN
DROP TABLE dbo.empdet;
END
Go
CREATE TABLE dbo.empmas
(
empid INT
)
Go
CREATE TABLE dbo.empdet
(
empdetid INT IDENTITY(1,1),
empid INT,
deactivated DATETIME
)
Go
INSERT INTO empmas
SELECT
1
UNION
SELECT
2
UNION
SELECT
3;
INSERT INTO dbo.empdet
SELECT
A.empid,
CASE WHEN A.empid = 1 THEN NULL ELSE DATEADD(Day, -A.empid, GETDATE()) END
FROM
empmas A CROSS JOIN
empmas B CROSS JOIN
empmas C
Go
SELECT * FROM empdet;
Go
CREATE TRIGGER t_del_emp ON [dbo].[empmas]
INSTEAD OF DELETE
AS
/* check for rows that could not be deleted and send an error message */
If exists(Select * from empdet E Inner JOIN deleted d on E.empid = D.empid AND E.deactivated IS NOT NULL)
BEGIN
RAISERROR('There are rows in this Delete that cannot be deleted', 16, 1);
END
/* Because this is an INSTEAD OF trigger the delete will not happen unless I do it again in the
trigger so we'll only delete rows that should be allowed to be deleted */
If exists(Select * from empdet E Inner JOIN deleted d on E.empid = D.empid and E.deactivated is null)
BEGIN
/* Update the deactivatable empdet rows that would tie to the empmas rows deleted */
UPDATE empdet
SET deactivated=getdate()
WHERE
EXISTS (SELECT 1 FROM DELETED d WHERE D.empid = empdet.empid) AND
deactivated IS NULL;
/* Because this is an INSTEAD OF trigger I have to redo the delete of the
appropriate empmas rows */
DELETE FROM empmas WHERE EXISTS (SELECT 1 FROM DELETED D WHERE empmas.empid = D.empid);
END
GO
/* should update the rows in empdet and delete the row in empmas */
DELETE FROM empmas WHERE empid = 1
Go
/* show the rows affected */
SELECT * FROM empmas;
SELECT * FROM empdet;
Go
/* should not delete the empmas row and not update the empdet rows*/
DELETE FROM empmas WHERE empid = 2
Go
/* show the rows not affected */
SELECT * FROM empmas;
SELECT * FROM empdet;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 5, 2010 at 7:21 pm
monkeytesiorna (8/4/2010)
CREATE TRIGGER t_del_emp ON [dbo].[empmas]
FOR DELETE
AS
declare @empid varchar(10)
SELECT @empid=d.empid FROM DELETED d
declare @empdetid INT
IF EXISTS(SELECT * FROM empdet WHERE empid=@empid AND deactivated is null)
BEGIN
SET @empdetid = (SELECT empdetid FROM empdet WHERE empid=@empid AND deactivated is null)
UPDATE empdet SET deactivated=getdate() WHERE empdetid=@empdetid
END
BEGIN TRANSACTION
if EXISTS (SELECT * FROM empdet WHERE empid=@empid)
BEGIN
RAISERROR 14001 'You cannot delete this Employee '
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
i want to update on other table if record has a relation on another table which field is deactivated is null or if is not null it will rollback..
if deactivated is
Ummm... my recommendation would be to spend a little time with Books Online and learn how to write triggers correctly, how a trigger interacts with transactions, and how to write a trigger that will handle more than one row at a time. I'm not trying to be mean here... there's just a whole lot that's fundamentally wrong with this trigger that demonstrates a basic misunderstanding of the code. If you don't understand it, you can't support it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply