August 31, 2006 at 12:47 pm
I have two databases on the same server. Database A has a table T with what amounts to a FK field (call it pageID). Database B has a "Page" table with PK pageID.
I'd like to prevent a user in B from deleting a Page record that is in use in A's table T.
A tirgger would be nice. Is there a BEFORE DELETE trigger, or do all DELETE triggers have to access the DELETED table? Your help greatly appreciated.
September 1, 2006 at 3:38 am
There are no BEFORE Triggers so you have to use an INSTEAD OF trigger or a SP.
September 2, 2006 at 4:42 am
I want to ask what type of sp can be used
September 3, 2006 at 10:33 am
>> I want to ask what type of sp can be used
You do not allow your users delete access to the table and force them to use a SP you have written. You can put whatever logic you want in a SP. If this is not a viable option, then use an instead of trigger.
September 4, 2006 at 1:57 am
Do you have any reference to the instead of delete trigger
September 5, 2006 at 7:33 am
Hi Mohammed ... search for "sql server instead delete trigger". This returns some very helpful sources. Be careful with the two magic tables with the unfortunate names "deleted" and "inserted". These are actually the names of local, transient tables, but since they are also words, it can get pretty confusing. Some people use all caps (DELETED, INSERTED) but not everyone does this.
Here is a trigger I use:
USE TrainingRecords
GO
DROP TRIGGER TRG_INSTEAD_OF_EVENT_DELETE
GO
CREATE TRIGGER TRG_INSTEAD_OF_EVENT_DELETE
ON Event
INSTEAD OF delete
AS
IF (NOT EXISTS (SELECT Etl.custEventID
FROM [VisionTest].[dbo].Employees_TrainingList Etl INNER JOIN deleted D
ON Etl.custEventID = D.EventUID ))
BEGIN
INSERT AUDIT_Event SELECT *,'DELETE' ,GETDATE() FROM Event
WHERE EXISTS
(SELECT * FROM deleted
WHERE Event.EventUID = deleted.EventUID)
DELETE FROM Event
WHERE EXISTS
(SELECT * FROM deleted
WHERE Event.EventUID = deleted.EventUID)
END
ELSE
BEGIN
INSERT AUDIT_Event SELECT *,'IN USE NOT DELETED' ,GETDATE() FROM Event
WHERE EXISTS
(SELECT * FROM deleted
WHERE Event.EventUID = deleted.EventUID)
END
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply