INSERT Trigger problem - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

INSERT Trigger problem

Author

Topic

royroes
Starting Member

2 Posts

Posted - 2005-02-03 : 07:24:28

Hi,
I'm using the follwing trigger:

CREATE TRIGGER [Insert_Installatie] ON [dbo].[tIns_Installaties]
FOR INSERT, UPDATE
AS
BEGIN
IF UPDATE(Klantnr)
BEGIN
IF EXISTS (SELECT inserted.Klantnr FROM inserted WHERE inserted.Klantnr IS NOT NULL AND inserted.Klantnr NOT IN (SELECT Rel_Relaties.klantnr FROM Rel_Relaties))
BEGIN
ROLLBACK TRAN;
RAISERROR ('INSERT/UPDATE statement conflicted with COLUMN FOREIGN KEY constraint ''FK_tIns_Installaties_tRel_Relaties_temp''. The conflict occurred in table ''tIns_Installaties'', column ''Klantnr''. The statement has been terminated.',14,3);
END
END
END

But when I insert a new record in tIns_Installaties table with a
klantnr that doesn't exist in tRel_Relaties_temp, the error isn't raised.
I've tested the trigger with some output lines, and I think the problem is in "AND inserted.Klantnr NOT IN (SELECT Rel_Relaties.klantnr FROM Rel_Relaties)"

But I can't figure out what the exact problem is.

Can anyone help me?

Roy

royroes
Starting Member

2 Posts

Posted - 2005-02-03 : 09:34:48

The problem was: "AND inserted.Klantnr NOT IN (SELECT Rel_Relaties.klantnr FROM Rel_Relaties WHERE Rel_Relaties.klantnr IS NOT NULL)"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources