INSERT trigger - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

INSERT trigger

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 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)

17689 Posts

Posted - 2013-06-21 : 00:38:30

[code]
if exists (select * from inserted where province = 'BC')
BEGIN
PRINT 'record will not update if province is not BC'
ROLLBACK TRANSACTION
END[/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 MVP
http://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
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!

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
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

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
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
[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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources