Conditional 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

Conditional Insert Trigger

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-14 : 04:48:42

Is there any way to create a "Conditional Insert Trigger"
My Scenario is this;
When a user adds an email address to the database, I want to look to see if the email address is like '%@acme-holdings%' and if it is then to change the value to 'Not allowed', otherwise to leave it alone and go ahead with inserting the original email address

TIA

ICW

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-14 : 05:04:52

Cant you validate this in your Front End application and send values accordingly?
In SQL, you can use

case when email like '%@acme-holdings%' then 'Not allowed' else email end

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-14 : 05:10:55

Hi ,thanks for the quick reply.
I can't do anything with the front end as it is a hard coded off the shelf product.
As I am a bit of a newbie here can I ask you a possibly naiive question?
WHen you say "case when email like '%@acme-holdings%' then 'Not allowed' else email end" How do you apply this? Is is part of the trigger or something else. If i am not being too cheeky could you give me an idea of the actual syntax?

best regards
ICW

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-14 : 05:37:53

Something like this

create trigger validate on yourTable for insert
as
update t1 set email=case when I.email like '%@acme-holdings%' then 'Not allowed' else I.email end
from yourTable t1 inner join inserted I on t1.PrimaryKeycol=I.PrimaryKeycol

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-14 : 06:18:07

Thanks a lot for the guidance;
I have just created and added the actual trigger (see below)
---------------------------
Use GoldMine
GO
create trigger validate_email on Contsupp for insert,update
as
update Contsupp set contsupref=case when I.contsupref like '%@acme-holdings%' then 'Not@allowed' else I.contsupref end
from contsupp cs inner join inserted I on cs.accountno=I.accountno
----------------------------
But I think I may have got something wrong because I have just added an acme-holdings email address and it wasn't changed.

TIA

ICW

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-14 : 06:22:46

SOrry My Mistake.
It appeared that the email address stayed but when I scrolled off the record and back again it said 'Not@allowed'

Thanks Again
ICW

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-14 : 06:32:13

Make sure that accountno is unique/Primary Key column

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-15 : 06:59:19

Hello,
I have my trigger working now but I also want to insert a row into a new table from the pseudo 'inserted' table. I know the syntax to do that, but can't quite figure out where to put it in?

Use GoldMine
GO
create trigger validate_email on Contsupp for insert,update
as
update Contsupp set contsupref=case when I.contsupref like '%@acme holdings%' then 'Not@allowed'
else I.contsupref end
from contsupp cs inner join inserted I on cs.accountno=I.accountno

new syntax to be added
Insert Into Email_validate (accountno, contsupref, Lastuser, lastdate, recid)
Select accountno, contsupref, lastuser, lastdate, recid
FROM inserted

thanks if u can help

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-15 : 07:07:25

I'm now answering my own questions doh!!
I used this and it seems to work ok.

Use GoldMine
GO
create trigger validate_email on Contsupp for insert,update
as
update Contsupp set contsupref=case when I.contsupref like '%@acme%' then 'Not@allowed'
else I.contsupref end
from contsupp cs inner join inserted I on cs.accountno=I.accountno
Insert Into Email_validate (accountno, contsupref, Lastuser, lastdate, recid)
Select accountno, contsupref, lastuser, lastdate, recid
FROM inserted
Where Inserted.contsupref like '%@acme%'
GO

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-15 : 07:12:48

How about one more thing!!
How about;

Xp_sendmail ‘admin@acme.com’, ‘Someone tried to add an acme email address again! Please run your report for details’

I am pretty sure I have to have SQL mail configured for this...is that right?
Also where can I insert this one (as I have never used it before)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources