sql trigger help?

[crystal]

I want a trigger that delete a row after an insertion. Can I do this?

Basically what I want is - after an insert, I check the values (2 columns) and if they are not what I want, I will delete them from the table.

 

[Snapster]

If you just want to only insert rows that match criteria (ie > 0 and < 100) then a constraint would be better than a trigger. If you want a trigger however then there shouldn't be a problem with what you want to do, just grab the values from the temporary 'inserted' table and test them.

 

[Markbnj]

Personally, I'd stay away from triggers. If you want to get rid of a row after an insert put the two operations in a stored procedure and wrap them up in a transaction. At least that way the behavior is explicit to anyone maintaining the app, and not buried away in the metadata.

 

[KLin]

What you want to do is prevent the insert from happening in the trigger. The other way to do this is a foreign key constraint. If a value doesn't exist in another table of values, then the insert cannot be committed.

In the trigger, you'd have something like the following

insert into table(PartNumber, PartDesc) VALUES (12345, 'Hammer')

CREATE TRIGGER [dbo].[Table_ITrig] ON [dbo].

FOR INSERT AS

If (SELECT PartDesc from inserted) = 'Hammer'
BEGIN
Raiserror('Hammers are not allowed', 16, 1)
Rollback Transaction
End

Thus the insert statement would fail and the user would get an error message saying "Hammers are not allowed."

 

[crystal]

Thanks for the help. Able to do it with a small trigger.

Didn't want to put the constraint on it because a service inserts the data - don't know what will happen if it throws an error since I didn't write the code for that.