insert trigger

[DTSFreak]

Hi,

I'm having trouble with an after insert trigger. The trigger handles an integrity check on a second id column.

I have a scheduled stored procedure which can return a recordset. If the id in that recordset does not exist in the table with the trigger it may insert the new id's. The problem is when i get a empty/no recordset, the trigger
fails the insert job saying it is not a valid id (insert).

Here are the statements i use:

INSERT:

INSERT INTO MyTable(id)
SELECT id from MyOtherTable
WHERE id not in (select id from MyTable)

TRIGGER:

CREATE TRIGGER Itrig_CheckId ON MyTable
FOR INSERT
AS
IF
(SELECT COUNT(*) FROM People, inserted
WHERE people.id = inserted.id) <1
BEGIN
RAISERROR ('not a valid id (insert)', 16, 1)
ROLLBACK TRANSACTION
END


How do i handle this properly?

 

[JamesLean]

This look like a simple foreign key - can't you just use a FK constraint?

If not, can you explain what tables you have and what logic you want to enforce - it's not quite clear to me?

--James