Solved

How do I identify what row has been updated within an update trigger

Posted on 2007-12-05
9
197 Views
Last Modified: 2010-03-19
How do I identify what row has been updated within an update trigger
0
Comment
Question by:paulCardiff
  • 3
  • 3
  • 3
9 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 20413747
You can not unless you have set up auditing for the table. SQL Server does not maintain that information.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20413963
can you eventually clarify the requirements?
0
 

Author Comment

by:paulCardiff
ID: 20414177
It quite striaght foward really i've got an orders table and i need to update another db every time an order is made.

If i can't do this out the box then i'm thinking i'll add an orderdate field but as this is new to me i'm wondering if this is the best way and even if it is - then how would the trigger systax look like ?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 20414211
this should give you some start:
CREATE TRIGGER trg_order

ON orders

FOR INSERT

AS

  UPDATE o   -- table alias of table to be updated

     SET somefield = somevalue   -- update part

  FROM othertable o   -- table to be updated, with alias name

  JOIN INSERTED i       -- INSERTED is the virtual table holding the rows updated in orders table

     ON i.ORDER_ID = o.ORDER_ID  -- join condition between the orders (inserted) and othertable tables.

  

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 27

Expert Comment

by:ptjcb
ID: 20414219
You should be careful about adding another trigger to the same transaction. Triggers are easy to forget and have been the cause of database performance issues.

You have one trigger that updates the orders table.

You want another trigger that fires on the update of the order table and updates another table in another database? Is this database always available? Is it on the same server?

0
 

Author Comment

by:paulCardiff
ID: 20414315
Thanks all

I must admit ptjcb performance is going to be crucial on this - so any tip or tricks would be much appreciated. i.e. are trigger quite resource hungry?

But to awnser your question yes this db is always available and will potentially be on the same server if theres a good argument for it

Also angelll its the update part i'm a bit stuck on sorry, if i did use the orderdate to identify how would i match it i.e. would it be to the nearest second / or minute?
0
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 250 total points
ID: 20414503
Triggers can be resource hungry - the other issue is that since all of their execution is in the background you may see a degradation when you write a query.

For example, Update order set column = new_data WHERE order_id = 40

Without the triggers that happens quickly. Add a trigger that updates data in another table.

Update order set column = new_data WHERE order_id = 40 and trigger fires updating the other table. Now you have a slightly slower transaction because it is doing two things instead of one. Also remember that you have locked that row in order and the other table because you are updating and it will stay locked throughout the transaction. Someone else needs to access that row information, they'll have to wait.

Add another trigger that updates another table in another database. Now you are locking three rows in three different tables in two different databases. Also for every table SQL Server will also update the index information.

Everything is slowing down just a little more.

Now six months pass. Your CEO decides all of the order prices are off by 10%. Your boss says that you need to update the prices. No problem, it's an easy query. But you have forgotten that there are triggers based on that table and you lock out everyone in the company while the update occurs and that takes quite a long time.

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20414974
>if i did use the orderdate
orderdate cannot be a primary key, can it?
maybe you explain a bit on what you are trying to "update" within the other table, ie what that other table is about?
0
 

Author Comment

by:paulCardiff
ID: 20415188
Sorry angelll what i mean is i need to indentify what row was last updated - but looking into it i can do it by max(date) i.e .max(orderdate).

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

939 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

10 Experts available now in Live!

Get 1:1 Help Now