December 31, 2012 at 9:07 am
the trigger is very simple and only makes some calculations. what I need is get the ID number of this record and write it to another field. why I'm asking is this is a server under very heavy load. every second tons of transactions inserts data in this table. I need to be sure that I will get ID number of this exact transaction that triggers the trigger.
CREATE TABLE BankTransactions
( ID Integer IDENTITY(1,1)
, myIDnumber int,
CustomerName nvarchar(100) )
ALTER trigger [dbo].[BankTransactionsInsert] on [dbo].[BankTransactions]
for insert
as begin
-- I need something like this, of course its wrong but explains what I need clearly:
-- UPDATE inserted SET myIDnumber = inserted.ID
-- or (but this does not work)
-- UPDATE BankTransactions SET myIDnumber = (SELECT SCOPE_IDINTITY())
end
December 31, 2012 at 10:38 am
aykut canturk (12/31/2012)
the trigger is very simple and only makes some calculations. what I need is get the ID number of this record and write it to another field. why I'm asking is this is a server under very heavy load. every second tons of transactions inserts data in this table. I need to be sure that I will get ID number of this exact transaction that triggers the trigger.
CREATE TABLE BankTransactions( ID Integer IDENTITY(1,1)
, myIDnumber int,
CustomerName nvarchar(100) )
ALTER trigger [dbo].[BankTransactionsInsert] on [dbo].[BankTransactions]
for insert
as begin
-- I need something like this, of course its wrong but explains what I need clearly:
-- UPDATE inserted SET myIDnumber = inserted.ID
-- or (but this does not work)
-- UPDATE BankTransactions SET myIDnumber = (SELECT SCOPE_IDINTITY())
end
You cannot update the INSERTED table. It is a virtual, read-only table there only for purposes to show us what changed with the update operation within the trigger. Also remember that in a trigger, in SQL Server, all rows that were affected by the DML operation are available in the INSERTED table, i.e. make sure you write your trigger in such a way where it can handle a DML operation that affects more than one row.
What may work for you is a MERGE statement. Something like this inside your AFTER trigger:
MERGE BankTransactions AS target
USING
( SELECT *
FROM INSERTED
) AS source
ON source.ID = target.ID
WHEN MATCHED
THEN UPDATE
SET target.myIDnumber = source.ID;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 31, 2012 at 12:48 pm
aykut canturk (12/31/2012)
the trigger is very simple and only makes some calculations. what I need is get the ID number of this record and write it to another field.
For the 'copied' field in the same table, does the copied field eventually change or will it always be in lock-step with the identity field? If it's in lock-step, you may have better luck with a calculated field.
The update to the other table on the scope_identity() should work fine, however. Are you having problems with that as well?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 1, 2013 at 7:46 am
opc.three's solution looks cool. I'm still testing it.
Actually I found many solutions about that. but I really want to be sure that any given solution works perfectly under heavy load. I'm talking about 100,000 transactions per day. The most reliable solution to me is actually inserting data then reading it back and get its ID number. I'm looking for a solution that works on sql server.
there are no calculated fields related this bank transactions. its basic insert statement.
I'm tracing records by ID number but soon there will be multiple sql servers collection independent data. so I need to track them. actually I wont write ID number to myIDnumber field. it would be "SERVER NAME" + ID number. so, yes, you can consider after getting ID number there will be no reliable relationship between ID and myIDnumber field.
to whom any concern, I trace records with ID number because I could not find any reliable solution, at least one that convinces me, to generate unique Transaction Number (instead of autoincrement ID) under very very huge web application loads. all solutions works on tests well of course but nobody said "I use this solution with 10 to 100 thousand hits per day, I recommend make it that way. I'm sure it will work."
as this application is directly manages money, I don't want to risk anything. yes, solutions which are reliable and tested but maybe slow is welcome. if one this transaction numbers repeats itself, even once, I really ... up.
January 1, 2013 at 8:08 am
What I provided is a set-based solution and it won't get much faster than that. If you want to store the current server name in the myIDnumber you could try something like this in your trigger:
MERGE BankTransactions AS target
USING
(
SELECT *
FROM INSERTED
) AS source
ON source.ID = target.ID
WHEN MATCHED
THEN UPDATE
SET target.myIDnumber =
CAST(@@SERVERNAME + CAST(ID AS NVARCHAR(11)) AS NVARCHAR(139));
If you simply want to be able to retrieve a new column that is made up of the server name and the value of the ID column on demand then you could add a computed column to your BankTransaction table, like this:
ALTER TABLE BankTransactions ADD myIDnumber
AS CAST(@@SERVERNAME + CAST(ID AS VARCHAR(11)) AS VARCHAR(100));
The only downsides are that each time you select the data the column will be re-computed, adding overhead to your SELECTs instead of your INSERTs. Also, you cannot persist a column like this because @@SERVERNAME is non-deterministic meaning that if you ever moved the database to a new server the value of the column may change, and that may be unacceptable.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply