• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

How can i ignore a specific column in a trigger

Dear Experts, I am using SQL Server2005.
I have made one trigger on INVNUM table there are almost 60 fields (e.g f1, f2, f3, f4,f5 . . . f60) in this table.
my requirement is my trigger must not fire if user make change in f3 field. kindly let me know how can i do this.
0
mahmood66
Asked:
mahmood66
  • 6
  • 5
  • 3
  • +1
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot avoid the trigger to fire.
but you can code in the trigger to process only those records where f3 is effectively changed...
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

you can use


IF NOT UPDATE(columname)
BEGIN
 --YOUR QUERY
END


so if above columnname is update trigger wont be run


-Bhavesh
0
 
Alpesh PatelAssistant ConsultantCommented:
Create Trigger TRigger name on table for update


Select f3 from updated
select f3 from Table

If f3(from updated table)<>f3(From Database)
'Do nothing
else
do things
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
mahmood66Author Commented:
Dear Brichsoft: I have implimented your given solution, i will update you if i face any issue regarding this.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note:

IF NOT UPDATE(columname)

will NOT tell you if the value has actually changed or not. it will just tell you if the column has been in the UPDATE ... SET columnname = xxx   syntax (or not).

if the value stays the same, the above will still return false !

you would need to compare INSERTED and DELETED tables (which are only available inside the trigger) to be 100% sure if the column's value was actually changed or not.
0
 
mahmood66Author Commented:
dear Agnellll !
hmmm, as per my requirement, if the value of specific field is updated, i do not want to fire the trigger, dear Agnell ! kindly let me know my criteria is correct or . . . . .

IF EXISTS(SELECT * FROM inserted i, deleted d WHERE  i.OrderDate >='01-May-2011'  and i.ucIDSOrdPORef<>d.ucIDSOrdPORef )

BEGIN
     code ----------
END
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

Yes your code is right with small change as you wanted ti not to run trigger if value changed.

IF EXISTS(SELECT * FROM inserted i, deleted d WHERE  i.OrderDate >='01-May-2011'  and i.ucIDSOrdPORef=d.ucIDSOrdPORef )
BEGIN
 -- YOUR QUERY COMES HERE
END


I changed "<>" to "=" condition.



Angel Sir - Thanks for clarification.

-Bhavesh
0
 
mahmood66Author Commented:
one more doubt.
 no need to make link (joining) between inserted and deleted table
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

you need to link the table.
when multiple rows are inserted or updated then data may be mismatch if we dont join the table.


-Bhavesh
0
 
mahmood66Author Commented:
is link between table is correct?

IF EXISTS(SELECT * FROM inserted i, deleted d WHERE I.AUTOINDEX = D.AUTOINDEX AND i.doctype=4 and i.OrderDate >='10-May-2011'  and i.ucIDSOrdPORef=d.ucIDSOrdPORef )

BEGIN

code
END
0
 
Bhavesh ShahLead AnalysistCommented:
hi,

if AUTOINDEX key is unique then it is right.

- bhavesh
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes,but the condition must indeed be:
IF EXISTS(SELECT * FROM inserted i, deleted d WHERE I.AUTOINDEX = D.AUTOINDEX AND i.doctype=4 and i.OrderDate >='10-May-2011'  and i.ucIDSOrdPORef <> d.ucIDSOrdPORef )

Open in new window

0
 
Bhavesh ShahLead AnalysistCommented:
Sir,

One doubt !!!
Author wanted not to run trigger if column value has changed.
so if he put this condition "i.ucIDSOrdPORef <> d.ucIDSOrdPORef
then he need to use


IF NOT EXISTS(SELECT * FROM inserted i, deleted d WHERE I.AUTOINDEX = D.AUTOINDEX AND i.doctype=4 and i.OrderDate >='10-May-2011'  and i.ucIDSOrdPORef <> d.ucIDSOrdPORef )


OR

IF EXISTS(SELECT * FROM inserted i, deleted d WHERE I.AUTOINDEX = D.AUTOINDEX AND i.doctype=4 and i.OrderDate >='10-May-2011'  and i.ucIDSOrdPORef = d.ucIDSOrdPORef )


Am i right?

-Bhavesh
0
 
mahmood66Author Commented:
i got something partially.
0
 
Bhavesh ShahLead AnalysistCommented:
Dear Author,

eXperts-eXchange having lots of lots of master brains & genius brains.

if you are not get what exactly you looking for, you can request moderator to re-open Question and ask for assistance from higher experts(In this post, Angel Sir(Genius of Genius) already gave opinion).

You can clarify your all doubts to Angel Sir.

I'm sure, you will get complete solution.


- Bhavesh
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now