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 |
learning_sql
Starting Member
3 Posts |
Posted - 2013-06-21 : 00:17:34
|
Hello,I am trying to create an insert trigger for the first time and have some difficulty getting the trigger below to work. I am using MS SQL Server 2008 R2.I want to create an INSERT trigger that will prevent the adding of a row with a province that is not BC. Essentially if the province is BC the trigger should allow the update to happen, if the province is not BC the trigger should prevent the row from being added. I know I need to use the inserted virtual table, but I am not sure what I am doing wrong. Any help would be very appreciated, thanks!// code to testinsert Employeesvalues (10, 'Brown', 'Joe', '21 Jump Street', 'Vancouver', 'BC', 'V6R 5Z4', '6045555555','1981-07-23')insert Employeesvalues (11, 'Brown', 'Joe', '21 Jump Street', 'Vancouver', 'ON', 'V6R 5Z4', '6045555555','1981-07-23')go//INSERT TriggerCREATE TRIGGER tr_insert_employeeON Employeesfor insertasif (select province from inserted) <> 'BC'BEGINPRINT 'record will not update if province is not BC'ROLLBACK TRANSACTIONEND |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-06-21 : 00:38:30
|
[code]if exists (select * from inserted where province = 'BC')BEGINPRINT 'record will not update if province is not BC'ROLLBACK TRANSACTIONEND[/code]you can achieve the same with constraint. No trigger required [code]alter table Employees add constraint chk_province check (province <> 'BC')[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-21 : 02:02:13
|
I would certainly go for constraint approach as suggested by Tan in these type of scenario. There's absolutely no need for trigger in this case. Even if you want to use a trigger, an instead of trigger might be better which will not allow insert to happen at all rather than completing action and then rollback.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
learning_sql
Starting Member
3 Posts |
Posted - 2013-06-22 : 14:24:15
|
Thanks for the input, I did end up getting this to work myself by using:CREATE TRIGGER tr_insert_employee ON Employees FOR INSERT ASIF (SELECT province FROM inserted) <> 'BC' BEGIN PRINT 'record will not update if province is not BC' ROLLBACK TRANSACTION ENDProbably not very pro, so I will look into the suggestions that have been offered. Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-23 : 02:14:01
|
quote: Originally posted by learning_sql Thanks for the input, I did end up getting this to work myself by using:CREATE TRIGGER tr_insert_employee ON Employees FOR INSERT ASIF (SELECT province FROM inserted) <> 'BC' BEGIN PRINT 'record will not update if province is not BC' ROLLBACK TRANSACTION ENDProbably not very pro, so I will look into the suggestions that have been offered. Thanks!
i would still suggest Tans suggestion or atleast an INSTEAD OF trigger------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-06-23 : 04:09:11
|
quote: Originally posted by learning_sql Thanks for the input, I did end up getting this to work myself by using:CREATE TRIGGER tr_insert_employee ON Employees FOR INSERT ASIF (SELECT province FROM inserted) <> 'BC' BEGIN PRINT 'record will not update if province is not BC' ROLLBACK TRANSACTION ENDProbably not very pro, so I will look into the suggestions that have been offered. Thanks!
by doing that you are assuming that inserted table will only contain 1 single row. Which might not be the actual case. Use the method that i posted earlier if you insists on using a trigger. Else a check constraint is preferable. KH[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 01:40:26
|
I still cant understand why its so difficult for OP to create a check constraint though!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|