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

Conditional Trigger

Posted on 2014-01-06
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
  • 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 Y.id = I.id and Y.groupId = I.groupID and I.DATE> Y.DATE)
--- do insert or update on your backup table here 



Open in new window

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 50

Accepted Solution

Lowfatspread earned 167 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 333 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 333 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 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