Trigger Help
-
I have written a trigger to update a field when data is inserted into a table (see code below)
DECLARE @IRN char(12)
SET @IRN = (SELECT IRN FROM inserted)
IF(SELECT COUNT(IRN) FROM ProductionControl WHERE IRN = @IRN) > 0
UPDATE ImagingEmu SET DA = 1
WHERE IRN = @IRN.
It works ok except that it updates all of the rows where the condition applies. I'd prefer it to just update the row which is being inserted. How can I accomplish this?
Thanks
RB
-
What's the primary key of the table?
As an aside, the way your trigger is written only caters for single row inserts. Try this
IF EXISTS (SELECT 1 FROM ProductionControl WHERE IRN IN (SELECT IRN FROM INSERTED))
UPDATE ImagingEmu SET DA = 1 WHERE [primary key] IN (SELECT [primary key] FROM INSERTED
Replace [primary key] with whatever the unique field is in your table. Not sure about the if statement. Some DDL would make things clearer.
hth
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for the reply,
Unfortunately there is no primary key in 'inserted'. This is for a request system. A primary key only gets generated when the request is inserted into the ProductionControle table.
Is there another way or referencing the row being inserted?
RB
-
The only way is to match what's in inserted with the real table is on a unique field or a unique combination of fields. SQL has no concept of row position.
Note that inserted has all the columns of the table that the trigger is on, and that triggers happen after the insert/update/delete has happened.
You could always add an identity field to get you something unique. The identity is auto populated when the record is inserted and it's always increasing.
Would you mind posting the definitions of the tables involved, some sample data and what it is that you're trying to do. It may be that someone here could see an alternative.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks
I've come up with this which seems to work ok.
CREATE TRIGGER [UpdateDA] ON [dbo].[ImagingEmu]
FOR INSERT
AS
DECLARE @IRN char(12)
SET @IRN = (SELECT IRN FROM inserted)
IF(SELECT COUNT(IRN) FROM ProductionControl WHERE IRN = @IRN) > 0
DECLARE @RequestNo int
DECLARE rn_cursor CURSOR
LOCAL SCROLL STATIC
FOR
SELECT RequestNo FROM ImagingEmu
OPEN rn_cursor
FETCH LAST FROM rn_cursor
INTO @RequestNo
UPDATE ImagingEmu SET DA = 1
WHERE IRN = @IRN
AND RequestNo = @RequestNo
CLOSE rn_cursor
DEALLOCATE rn_cursor
I think I need to stick with one row at time because each row has a different value for IRN which is what needs to be checked in the ProductionControl table.
RB
-
I hate cursors. There's usually a better way....
The way you've written it, if there are multiple rows inserted at once, only one of them will be processed for updating ImagingEmu. (Whichever one SQL picks from inserted for the variable assignment) Bear in mind that a trigger will execute only once for an insert, whether you insert 1 record or 1 million records.
Also the entire thing of get RequestID from ImagingEmu then use it to match records from ImagingEmu doesn't make any sense. You might as well match only on IRN like earlier. I think that should have been DECLARE CURSOR ... FOR SELECT ResourceID FROM inserted
Try this instead of the whole cursor. If I've understood what you're doing (and there's a definate posibility that I haven't) this should do what you want.
Update ImagingEmu SET DA = 1
WHERE EXISTS (SELECT 1 FROM inserted WHERE inserted.IRN=ImagingEmu.IRN AND inserted.RequestNo = ImagingEmu.RequestNo) AND IRN IN (SELECT IRN FROM ProductionControl GROUP BY IRN HAVING Count(*)>1)
Find the records in ImagingEmu that match the just inserted rows (matching on IRN and ResourceID and have more than one matching record in ProductionControl (matching on IRN) and set the value of DA to one.
As I said before, table definitions would make it a lot easier to give useful advice.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Roger
You wouldn't be a developer rather than a DBA would you
If you tried to put a trigger with a cursor into production at our site you'd be lucky to keep your ears. Cursors are bad enough for performance, but to include one in a trigger should be classed as the eighth deadly sin
As mentioned in a prior post, how about posting the DDL for the tables in question and explaining what has to happen. Then as a whole the community will help redeem your soul
--------------------
Colt 45 - the original point and click interface
-
You wouldn't be a developer rather than a DBA would you
Be nice. I'm a developer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
"Be nice. I'm a developer..."
That's ok, I forgive you because "I hate cursors..."
--------------------
Colt 45 - the original point and click interface -
Apologies if this message is posted twice but the first time I posted it nothing seemed to happen.
Thanks for the above comments. It will take me a little while to process them and provide the information required.
I am a developer (sort of) but this is my first foray into SQL triggers and T-SQL. I probably should have posted my original message to the T-SQL forum but then my soul may have been lost forever.
In the meantime, I take the point that using cursors may have a high overhead but I couldn't think of another way of accessing the last inserted record. I need to do this because the field I want to update isn't in 'inserted' ('inserted' is only a stub of the new record).
Also, I'll have to think further about updating more than one record. Each record is inserted separately into ImagingEmu by the client application and each contains a different value for IRN which needs to be checked for in the ProductionControl table.
RB
-
'inserted' is only a stub of the new record
Not sure what you mean by that. inserted contains all the fields that are in the table the trigger is on. If you mean that only a couple of fields are populated when the insert occurres and the rest updated later?
Did you try my single update statement?
Awaiting the table definition and example data.
There's no shame in been new. We all had to start somewhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Seriously, if any of my collegues submitted a trigger with cursors, it would be back on their desk so fast they'd have whiplash.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply