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.
| Author |
Topic |
|
laailalalaa
Yak Posting Veteran
57 Posts |
 Posted - 2009-05-04 : 09:50:01
|
| hellohow can i cancel the operation that triggered.. a trigger in some cases?say i have an after insert trigger for a Students(id int, name varchar(15)) table and if i try to insert a student with an empty name i want the trigger to raise an error and cancel the insert operation.thank you |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-04 : 10:11:09
|
| [code]CREATE TRIGGER ins_Students ON Students FOR INSERT ASSET NOCOUNT ONIF EXISTS(SELECT * FROM inserted WHERE name='' OR name IS NULL) BEGIN ROLLBACK TRANSACTION RAISERROR('Name column cannot be empty.',16,1)END[/code]A better way to do it is to have a CHECK constraint on that column:[code]ALTER TABLE Students ADD CONSTRAINT CHK_BlankName CHECK(Name>'' AND Name IS NOT NULL)[/code]The column should also be defined NOT NULL, then you don't even need the 2nd half of the constraint. |
 |
|
|
laailalalaa
Yak Posting Veteran
57 Posts |
Posted - 2010-04-27 : 11:25:27
|
| yep, i got to it as well. only that i forgot to post here. thanks :) |
|
|
|
|
|
|
cancel the 'triggering' operation