Trigger - no action - 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

Trigger - no action

Author

Topic

Wobeca
Starting Member

12 Posts

Posted - 2011-05-26 : 19:14:33

Hi there,

I have a trigger which performs an action on a table after an update.
This update only happens if the given criterium in the trigger is 'true'.

The problem is, if the given criterium is 'false', a 'null' value is trying to be inserted in that table, and a 'null' value isn't accepted.

Is there any way that I can let the trigger perform no action until this value is 'not null'?

In other words, when the given criterium is false, I want the trigger to 'do nothing'.

Any advise on this?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-26 : 19:36:28

Show us the trigger so we can help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-26 : 19:39:51

If the update happens only if a criterion in the trigger is true, is that a circular requirement? Or may be I misunderstood. If you just want the trigger to perform an action based on some condition, you can check for the condition as follows:

create trigger dbo.YourConditionalTrigger
   on dbo.YourTable
   after insert,update,delete
as
begin
   if (certain_criterion = 1)
   begin
      -- do your stuff here
   end
end
go

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-27 : 05:52:34

just to clarify, the trigger will be fired AFTER the insert/update/delete statement ..

if i understood correctly,
try an (certain_criterion IS NOT NULL) right before your IF statement

IF (certain_criterion IS NOT NULL)
if (certain_criterion = 1)

Where software development knowledge meets the reader

Wobeca
Starting Member

12 Posts

Posted - 2011-05-27 : 15:23:18

Thank you all for your effort and input.
I was able to solve it myself though.

I made a mistake on which table the trigger should be fired.
After adapting this, I was able to let the trigger do his thing.

Sorry for the inconvenience, I'm very new at this...

Thanks!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-29 : 17:45:50

There is seldom any need for TRIGGERs at all in modern SQL. Get someone to help you replace it with DRI actions and constraints.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-30 : 01:51:52

Really Joe? Never needed to audit data changes?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources