Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-24 : 07:41:55
|
| Gary writes "Through triggers, I need to do 3 things.there is an ODS db, which has normal processing activities (inserts, updates, and deletes). There is also a ODSFull db, which reflects the most current info only for each record, even if it has been deleted from the ODS db.So, in an Insert trigger I have an insert into the ODSFull from the INSERTED record in ODS - no problems here.For the Update in ODS, what I need to do is update the fields in the corresponding record in the ODSFull db. (where sysId fields match) - I am struggling with getting an efficient way to do this. Currently I am doing a select of each field individually into a declared variable, then updating the ODS full record, whic is very slow (13 selects for a row in a 13 field table). No matter the variation I use I can not seem to get it in one neat statement like teh Insert.Same situation with the Delete, essentially, I will not be deleting the record, but tagging a status as 'Deleted' in an additional field within the ODSFull version of the record, where sysIDs match." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-08-24 : 07:46:48
|
| It's easier to simply delete the related row(s) from the ODSFull database and then insert the updated row(s) from the inserted table. That gives you the "one neat statement" you're looking for (ok, TWO neat statements, still less than 13). In fact, you can use a single trigger for both INSERTs and UPDATEs:CREATE TRIGGER trg_MoveToODSFull ON myTable FOR INSERT, UPDATE ASSET NOCOUNT ONIF EXISTS(select * from deleted)BEGINDELETE FFROM ODSFull..myTable F INNER JOIN inserted I ON F.ID=I.IDENDINSERT ODSFull..myTable SELECT * FROM inserted |
 |
|
|
|
|
|
|
|
Updating records through triggers