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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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



Open in new window

Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.


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


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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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?
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.
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.
cat4larryAuthor Commented:
As usual, @Anthony Perkins was spot on with his assistance!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.