Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Conditional Trigger

Posted on 2014-01-06
Medium Priority
Last Modified: 2014-01-08
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

Question by:cat4larry
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
LVL 16

Expert Comment

by:Surendra Nath
ID: 39760207
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...

Author Comment

ID: 39760260
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.
LVL 16

Expert Comment

by:Surendra Nath
ID: 39760490
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 50

Accepted Solution

Lowfatspread earned 668 total points
ID: 39762258

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.

Author Comment

ID: 39762441
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.
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1332 total points
ID: 39762979
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?

Author Comment

ID: 39763480
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.
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1332 total points
ID: 39763872
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.

Author Closing Comment

ID: 39766700
As usual, @Anthony Perkins was spot on with his assistance!

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question