Solved

Conditional Trigger

Posted on 2014-01-06
9
219 Views
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

Thanks
0
Comment
Question by:cat4larry
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
Comment Utility
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
 

Author Comment

by:cat4larry
Comment Utility
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
 
LVL 16

Expert Comment

by:Surendra Nath
Comment Utility
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 167 total points
Comment Utility
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:cat4larry
Comment Utility
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 333 total points
Comment Utility
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
 

Author Comment

by:cat4larry
Comment Utility
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 333 total points
Comment Utility
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
 

Author Closing Comment

by:cat4larry
Comment Utility
As usual, @Anthony Perkins was spot on with his assistance!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

728 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now