Trigger between to Database -- sql server msde

sql server msde : Trigger between to Database

Andrea Montanari
10/17/2005 12:00:00 AM

hi,
[quoted text, click to view]

SET NOCOUNT ON
USE master
CREATE DATABASE a
GO
CREATE DATABASE b
GO
USE a
GO
CREATE TABLE dbo.masterT (
ID int not null PRIMARY KEY
)
INSERT INTO dbo.masterT VALUES ( 1 )
INSERT INTO dbo.masterT VALUES ( 2 )
INSERT INTO dbo.masterT VALUES ( 3 ) -- NOT REFERENCED
GO
USE b
GO
CREATE TABLE dbo.detailT (
ID int NOT NULL IDENTITY PRIMARY KEY ,
IdMaster int NOT NULL
)
INSERT INTO dbo.detailT VALUES ( 1 )
INSERT INTO dbo.detailT VALUES ( 2 )
INSERT INTO dbo.detailT VALUES ( 2 )
GO
USE a
GO
CREATE TRIGGER tr_D_masterT ON dbo.masterT
FOR DELETE
AS BEGIN
IF @@ROWCOUNT = 0 RETURN

IF EXISTS (
SELECT IdMaster FROM b.dbo.detailT t JOIN deleted d ON t.IdMaster=d.ID
)
BEGIN
RAISERROR ('Referenced articles can not be deleted', 16 , 1 )
ROLLBACK
RETURN
END
END
GO
PRINT 'delete ok'
DELETE dbo.masterT
WHERE ID = 3

PRINT 'delete failure'
DELETE dbo.masterT
WHERE ID = 1

GO
USE master
GO
DROP DATABASE a
DROP DATABASE b
--<--------
....
delete ok
delete failure
Server: Msg 50000, Level 16, State 1, Procedure tr_D_masterT, Line 10
Referenced articles can not be deleted
....
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://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

Islamegy®
10/17/2005 12:00:00 AM

What if I'm my trigger can't find the second database ans say "b" is invalid
object "b is exist database on my server "??

[quoted text, click to view]

Islamegy®
10/17/2005 12:00:00 AM

What if I'm my trigger can't find the second database ans say "b" is invalid
object [b is exist database on my server]??

[quoted text, click to view]

Andrea Montanari
10/17/2005 12:00:00 AM

hi,
[quoted text, click to view]

if the trigger is not able to find the related db at all, the delete
statement fail with the
Server: Msg 208, Level 16, State 1, Procedure tr_D_masterT, Line 6
Invalid object name 'b.dbo.detailT'.
error reported, like

SET NOCOUNT ON
USE master
CREATE DATABASE a
GO
USE a
GO
CREATE TABLE dbo.masterT (
ID int not null PRIMARY KEY
)
INSERT INTO dbo.masterT VALUES ( 1 )
GO
CREATE TRIGGER tr_D_masterT ON dbo.masterT
FOR DELETE
AS BEGIN
IF @@ROWCOUNT = 0 RETURN

IF EXISTS (
SELECT IdMaster FROM b.dbo.detailT t JOIN deleted d ON t.IdMaster=d.ID
)
BEGIN
RAISERROR ('Referenced articles can not be deleted', 16 , 1 )
ROLLBACK
RETURN
END
END
GO
PRINT 'delete fails'
DELETE dbo.masterT
WHERE ID = 1
GO
PRINT 'no delete will be executed'
SELECT * FROM dbo.masterT

GO
USE master
GO
DROP DATABASE a
--<----------
delete fails
Server: Msg 208, Level 16, State 1, Procedure tr_D_masterT, Line 6
Invalid object name 'b.dbo.detailT'.
no delete will be executed
ID
-----------
1

--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://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

Islamegy®
10/17/2005 1:41:56 PM

While I discovered there is no way to create FK relation betwwen 2 table in
diffrent Database & I need to check integrity between them..
So i'm trying to use For Delete Trigger but I'm unable to make my trigger
refrence to second database. How could i implement trigger work with 2
database..
Second.. How could i make my C# application detect if the row deleted or the
trigger restored it??

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server msde

home · blog · groups

Questions? Comments? Contact the dn