SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 INSERT trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_sql
Starting Member

3 Posts

Posted - 06/21/2013 :  00:17:34  Show Profile  Reply with Quote
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 test
insert Employees
values (10, 'Brown', 'Joe', '21 Jump Street', 'Vancouver', 'BC', 'V6R 5Z4', '6045555555','1981-07-23')

insert Employees
values (11, 'Brown', 'Joe', '21 Jump Street', 'Vancouver', 'ON', 'V6R 5Z4', '6045555555','1981-07-23')

go


//INSERT Trigger
CREATE TRIGGER tr_insert_employee
ON Employees
for insert
as
if (select province from inserted) <> 'BC'
BEGIN
PRINT 'record will not update if province is not BC'
ROLLBACK TRANSACTION

END

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 06/21/2013 :  00:38:30  Show Profile  Reply with Quote

if exists (select * from inserted where province = 'BC')
BEGIN
PRINT 'record will not update if province is not BC'
ROLLBACK TRANSACTION
END


you can achieve the same with constraint. No trigger required
alter table Employees add constraint chk_province check (province <> 'BC')



KH
Time is always against us


Edited by - khtan on 06/21/2013 00:39:01
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/21/2013 :  02:02:13  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

learning_sql
Starting Member

3 Posts

Posted - 06/22/2013 :  14:24:15  Show Profile  Reply with Quote
Thanks for the input, I did end up getting this to work myself by using:

CREATE TRIGGER tr_insert_employee
ON Employees
FOR INSERT
AS
IF (SELECT province FROM inserted) <> 'BC'
BEGIN
PRINT 'record will not update if province is not BC'
ROLLBACK TRANSACTION
END

Probably not very pro, so I will look into the suggestions that have been offered. Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/23/2013 :  02:14:01  Show Profile  Reply with Quote
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
AS
IF (SELECT province FROM inserted) <> 'BC'
BEGIN
PRINT 'record will not update if province is not BC'
ROLLBACK TRANSACTION
END

Probably 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 06/23/2013 :  04:09:11  Show Profile  Reply with Quote
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
AS
IF (SELECT province FROM inserted) <> 'BC'
BEGIN
PRINT 'record will not update if province is not BC'
ROLLBACK TRANSACTION
END

Probably 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
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/24/2013 :  01:40:26  Show Profile  Reply with Quote
I still cant understand why its so difficult for OP to create a check constraint though!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000