Trigger on Last record
-
I have a trigger that fires when a record is updated in a table. This is a very crude trigger as it truncates the table and re-inserts all records from the source table.
What is the most efficient manner of selecting the very last record that was entered into the table?
Kindest Regards,Nick
-
By chance, does your table have an identity column?
What is the schema of this table?
And, because people are going to ask, can you tell us a little more about this process?
Thanks
SQL guy and Houston Magician
-
Hi,
Unfortunately the table has no identity column. There are date and time fields would it be possible to perform an extract based on the time of the last record in the target table against the records in the source table?
Kindest Regards,Nick
-
There's no first or last record in a table. You just have data kept without any particular order (well order of the clustered index... but that's all).
If you want first or last record, you need and identity column, or a datetime column with a default of getdate() (or manually set) when the record is inserted. If you don't have that then you can't determine the last record, unless you have a business rule that can tell what is supposed to be the last record. When you can answer that question then the query will basically write itself.
-
Do you use system table inserted inside of the trigger?
_____________
Code for TallyGenerator -
If I concatenated date + time to Eg 01/12/2006 08:15:13 for the entered record how would i write the following query:
Source table Last record in date field SOURCEDATE 01/12/2006 08:15:13
Target table Last record in date field TARGETEDATE 30/11/2006 06:00:00
Select from SOURCETABLE where SOURCEDATE < TARGETDATE.
This would need to select all the records in the source table that are no in the target table.
Does this explain my query?
Kindest Regards,Nick
-
-
Why is this even a trigger ? Sounds like typical source/target table synchronisation.
- Identify primary key column or columns
- Insert target where primary key exist in source but not target
- Update target where primary key exists but 1 or more non-key columns differ
- Delete target where primary key no longer exists in source
-
I'm getting more confused, the farther I read thru the thread.
-
If you can delete everything in the table, why can't you add an IDENTITY column? This isn't making any sense...
The topper is separate date/time columns... one of those not so rare forms of "Death by SQL".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply