Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Conditional Trigger

I need an Update Trigger that only changes data conditionally.  

So basically here's the logic.  If a row is updated the trigger fires.  Then there should be t-sql that says "If I'm the MAX Row for this specific ID, change the values in Table y".   I know how to do everything but the "if I'm the Max Row".  Meaning that I don't know how to add "complex" logic to a trigger

Thanks
0
cat4larry
Asked:
cat4larry
  • 4
  • 2
  • 2
  • +1
3 Solutions
 
Surendra NathTechnology LeadCommented:
what  do you mean by MAX row, can you please explain a little more...
it would be good, if you can give us some data as well...
0
 
cat4larryAuthor Commented:
Let's assume this is the data

ID    GroupID     Date
1      1                  1/1/2013
2      1                  1/2/2013
1      2                  1/1/2013
2      2                  1/2/2013

This is totally just dummy data, cause that's not really important.  Like I said, I know how to find a MAX of something, I just don't know how to add complex logic to a Trigger.

Anyway, let's say I want the ID of the row, grouped by GroupID, that has the most current date.  Then in the trigger it should have logic like this:   if the row that was updated is the max row (based on the logic I gave you for what a "max" row is) then also update Table Y

Perhaps this will help.  I have a table.  It currently has an insert Trigger on it.  So great, everytime a new row gets inserted, Table Y get's updated.  HOWEVER, we also run clean up processes on the table from time to time.  For instance, let's say we find that we had a bug that was causing erroneous data in that table, we might run a clean up process to fix it.  But that also could mean that the data in Table Y is incorrect.  I need to make sure that when the data gets updated that if the Max Row was one that was updated that Table Y is updated also.  Where Max Row = the most current row that was INSERTED.  Get it, I'm needing a work around for the fact that there isn't always a Insert Trigger in play.

I hope that makes sense.
0
 
Surendra NathTechnology LeadCommented:
I believe in this case, you are telling me the max row as the latest row (latest date).

if that is the case you can use the sample trigger logic below

create trigger trg_test
for insert,update,delete 
BEGIN

if exists ( select 1 from inserted)
BEGIN
   IF EXISTS ( SELECT 1 FROM <your table> Y, inserted I where Y.id = I.id and Y.groupId = I.groupID and I.DATE> Y.DATE)
--- do insert or update on your backup table here 

END


END

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LowfatspreadCommented:
NO!

1. your trigger doesn't get fired when an "UPDATE" to the data occurs, it gets processed whenever the originating sql statement type gets executed...

             DATA DOES NOT NEED TO CHANGE FOR THE TRIGGER TO FIRE

2. The trigger fires for the Statement , Multiple table rows can be affected by the underlying statement...

I belive in your scenario that what you are saying is

i)  for each unique id,groupid   combination affected by the SQL operation
    you may need to perform an update on tableY...

This "update" could be an Insert ,update or delete action depending on what has happend to the source table.
0
 
cat4larryAuthor Commented:
it gets processed whenever the originating sql statement type gets executed

please explain to me what this means.  Keep in mind that I rarely have ever used a trigger in my life so I'm very much a noob with them.
0
 
Anthony PerkinsCommented:
please explain to me what this means
For example, assume you have an UPDATE TRIGGER on YourTable and execute the following UPDATE statement:
UPDATE YourTable
SET Active = 1
WHERE <some condition>

Which affects 100 rows (of which 10 never changed).

The TRIGGER will fire only once and all 100 rows will be in the INSERTED/DELETED logical tables.

It is your job, to use these logical tables to identify which data actually was affected.

Is that clear?
0
 
cat4larryAuthor Commented:
Yep. Completely.  

So now let me regurgitate back to make sure I really am:

1) I create a INSERT trigger on a Table
2) an insert statement is run which inserts 50 rows
3) 50 rows get inserted into the table AND
4) sql takes a copy of those 50 rows and puts them in some sort of temp logical table
5) I then access the inserted logical tables and do what I will with that data

So, what is the performance cost of a trigger?  Cause I'm starting to think that I will want an INSERT trigger but not an UPDATE.  If I have to run a bulk update due to "bad data" I will just need to remember to update the data in the other table as well where the "other table" is the one I originally was going to change when the UPDATE trigger fired.
0
 
Anthony PerkinsCommented:
So now let me regurgitate back to make sure I really am:
That is accurate.

So, what is the performance cost of a trigger?
Try and keep it as lean as possible.  For example using a CURSOR or sending email in a Stored Procedure is a very bad idea.

If I have to run a bulk update due to "bad data"
If you are importing data, then there may not be any need for a TRIGGER.  All you have to do is import the data into a staging table that is truncated before you start the import.  This way you can do all your compares to this staging table and you can validate what you want to insert into your (final) Production tables.
0
 
cat4larryAuthor Commented:
As usual, @Anthony Perkins was spot on with his assistance!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now