Trigger for Referential Integrity

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger for Referential Integrity

Author

Topic

Kev
Starting Member

2 Posts

Posted - 2009-10-20 : 07:00:28

Hi all

I need to create a trigger to enforce referential integrity between two separate databases (on the same server). I'm fairly new to SQL so am trying to test my coding and syntax before applying it to the required situation. A lot of other sites have advised not to pursue the seperate database method but I can't get around it.

I have created two tables (one on each database) - Test1.dbo.test_Customer and Test2.dbo.test_Customer. I need to enforce the primary/foreign key relationship between the two tables so that a value can only be entered into the Test2.dbo.test_Customer where the value exists in the other table.

This is the trigger that I've come up with

"
USE Test2
GO
CREATE TRIGGER CustomerNoCheck
ON test_Customer
AFTER INSERT
AS
IF NOT EXISTS (SELECT c1.customerNo
FROM Test1.dbo.test_Customer c1, Test2.dbo.test_Customer c2
WHERE c1.customerNo = c2.customerNo
)
BEGIN
RAISERROR ('The Customer Number entered does not exist. Please enter a valid Customer Number.', 10, 1);
ROLLBACK TRANSACTION;
END;
GO
"

I have then tried to test the trigger, by entering values into the Test2 table, to confirm that it works. When I enter a value that exists in the Test1 table the insert value statement is accepted and when I enter a value that does not exist in the Test1 table the insert statement is rolledback and the error statement is raised. So it seems to work .....But if I insert a value that exists in the Test1 table, then after that try to enter a value that doesn't exist in the Test1 table, both insert statements are accepted (even though I know the last statement shouldn't).

So my query is....is there a way to amend my trigger so that I can stop an incorrect value from being accepted after a correct value??

Sorry if it's a bit long winded but I'd really appreciate your help. This is my first post; let me know if you need any further info.

Thanks for your help in advance

Kev

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-10-20 : 07:16:34

[code]
USE Test2
GO
CREATE TRIGGER CustomerNoCheck
ON test_Customer
AFTER INSERT
AS

IF EXISTS
(
SELECT *
FROM inserted I
WHERE NOT EXISTS
(
SELECT *
FROM Test1.dbo.test_Customer C
WHERE C.customerNo = I.customerNo
)
)
BEGIN
RAISERROR ('The Customer Number entered does not exist. Please enter a valid Customer Number.', 16, 1);
ROLLBACK TRANSACTION;
END;
GO
[/code]

Kev
Starting Member

2 Posts

Posted - 2009-10-20 : 21:48:03

Great!

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources