SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Referential Integrity
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

vinayj
Starting Member

32 Posts

Posted - 06/03/2005 :  09:45:48  Show Profile
I have two tables in two seperate databases. I want to have foreign key constraints on these tables. What is the best method to do so other than TRIGGERS. If triggers is the only way can any one give me a code example.

Vinay

X002548
Not Just a Number

15586 Posts

Posted - 06/03/2005 :  10:32:32  Show Profile
No Cross database constraints...Triggers it is:


SET NOCOUNT ON
GO

USE Northwind
GO

CREATE TABLE myTable99(Col1 int IDENTITY(1,1) PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2) SELECT 'x' UNION ALL SELECT 'y' UNION ALL SELECT 'z'
GO

SELECT * FROM myTable99
GO

USE Pubs
GO

CREATE TABLE myTable00(Col0 int IDENTITY(1,1) PRIMARY KEY, Col1 int, Col2 char(1))
GO

CREATE TRIGGER myTrigger00 ON myTable00 FOR INSERT, UPDATE
AS
  BEGIN
	SET NOCOUNT ON
	DECLARE @Error_Message varchar(100)
	IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
		IF EXISTS(SELECT * FROM inserted i
			   WHERE NOT EXISTS(SELECT * 
					      FROM Northwind.dbo.myTable99 t
					     WHERE i.Col1 = t.Col1))
		  BEGIN
			SET @Error_Message = 'Foreign Key Constraint violation for INSERT Against Northwind.dbo.myTable99'
			GOTO Trigger_Error
		  END

	IF EXISTS(SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
		IF EXISTS(SELECT * FROM inserted i
			   WHERE NOT EXISTS(SELECT * 
					      FROM Northwind.dbo.myTable99 t
					     WHERE i.Col1 = t.Col1))
		  BEGIN
			SET @Error_Message = 'Foreign Key Constraint violation for UPDATE Against Northwind.dbo.myTable99'
			GOTO Trigger_Error
		  END

	Trigger_Exit:
		SET NOCOUNT OFF
		Return

	Trigger_Error:
		ROLLBACK TRAN
		RAISERROR 50001 @Error_Message
		GOTO Trigger_Exit
  END
GO

USE Northwind
GO
CREATE TRIGGER myTrigger99 ON myTable99 FOR DELETE
AS
  BEGIN
	SET NOCOUNT ON
	DECLARE @Error_Message varchar(100)
	IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
		IF EXISTS(SELECT * FROM deleted i
			   WHERE EXISTS(SELECT * 
					  FROM Pubs.dbo.myTable00 t
					     WHERE i.Col1 = t.Col1))
		  BEGIN
			SET @Error_Message = 'Foreign Key Constraint violation for DELETE Against Pubs.dbo.myTable00'
			GOTO Trigger_Error
		  END

	Trigger_Exit:
		SET NOCOUNT OFF
		Return

	Trigger_Error:
		ROLLBACK TRAN
		RAISERROR 50001 @Error_Message
		GOTO Trigger_Exit
  END
GO

USE Pubs
GO
			   
INSERT INTO myTable00(Col1, Col2) SELECT 1,'x'
GO

SELECT * FROM myTable00
GO

INSERT INTO myTable00(Col1, Col2) SELECT 4,'a'
GO
SELECT * FROM myTable00
GO

UPDATE myTable00 SET Col1 = 4 WHERE Col1 = 1
GO
SELECT * FROM myTable00
GO

USE Northwind
GO

DELETE FROM myTable99 WHERE Col1 = 1
GO

SELECT * FROM myTable99
GO
	   
USE Pubs
GO
DROP TRIGGER myTrigger00
DROP TABLE myTable00
GO


USE Northwind
GO
DROP TABLE myTable99
SET NOCOUNT OFF
GO




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000