Before Delete

I have 2 databases "Law","Rules" .. the second have tables which is linked
to the first one... so i want to deny Deleting of Record from first if it
has a child record in the other database...
I notice that there is no "Before Delete" trigger in sql server so how could
i control deleteing records from first database..
Second.. how could i roll-back Delete or update operation?
Did you consider a Foreign key Constraint for that ? If it is not
applicable you can do a ROLLBACK within a trigger and raise an error to
show up the error to the user.
http://groups.google.de/group/micros...18307e92ac868c
HTH, Jens Suessmeyer.
|||Instead of trying ot use a trigger, how about applying a foreign key
constraint instead? Then when you try to delete a row from the first
table you'll get an error if there's a dependent row in the second
table. It'll also be much faster than using a trigger.
On Sat, 8 Oct 2005 16:16:51 +0200, "Islamegy" <Islamegy@.Private.4me>
wrote:

>I have 2 databases "Law","Rules" .. the second have tables which is linked
>to the first one... so i want to deny Deleting of Record from first if it
>has a child record in the other database...
>I notice that there is no "Before Delete" trigger in sql server so how could
>i control deleteing records from first database..
>Second.. how could i roll-back Delete or update operation?
>
|||hi,
bradsbulkmail@.comcast.net wrote:[vbcol=seagreen]
> Instead of trying ot use a trigger, how about applying a foreign key
> constraint instead? Then when you try to delete a row from the first
> table you'll get an error if there's a dependent row in the second
> table. It'll also be much faster than using a trigger.
> On Sat, 8 Oct 2005 16:16:51 +0200, "Islamegy" <Islamegy@.Private.4me>
> wrote:
have you tried something like
SET NOCOUNT ON
CREATE DATABASE a
CREATE DATABASE b
GO
USE a
CREATE TABLE dbo.m (
Id int NOT NULL PRIMARY KEY ,
Descr varchar (10) NOT NULL
)
GO
USE b
GO
CREATE TABLE dbo.d (
ID int NOT NULL PRIMARY KEY ,
IdRif int NOT NULL
CONSTRAINT fk_d_m FOREIGN KEY
REFERENCES a.dbo.m (Id) ,
Descr varchar (10) NOT NULL
)
GO
USE master
GO
DROP DATABASE a
DROP DATABASE b
?
the actual result is
Server: Msg 1763, Level 16, State 1, Line 1
Cross-database foreign key references are not supported. Foreign key
'a.dbo.m'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply