INSERT Trigger, cancel insert 1

[sunaj]

Hi,

I want to make a trigger that validates the INSERT data. If the data does not meet the criterias the INSERT should be cancelled. What is the best approach?
TRIGGER AFTER and then DELETE inserted row?

I also need to make sure that the data cannot be changed by an UPDATE. I could do another trigger (because in this cause I do not want to delete the row, just not update it). Or can I check in the trigger wheater it is an UPDATE or an INSERT?

Is there a better way to do this? Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[sunaj]

ps. My problem would be solved if I could make foreign keys between 2 tables in different databases - is that possible? Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[osjohnm]

Hi sunaj

You can't setup foreign keys across databases as sql server doesn't support it. I'm not sure if there are RDMS that support it.

You would have to use ROLLBACK eg.

CREATE TRIGGER TEST
FOR INSERT
AS

...what ever you criteria check is
BEGIN
ROLLBACK
END

If you post your trigger or how you check the criteria we can maybe give more assistance.

John

 

[SQLSister]

You could make sure no one has update rights on the table to prevent updates. Or you could use an instead of update trigger to keep any transactions from happening.