do operation on last inserted record by a trigger

Question

do operation on last inserted record by a trigger

dr_csharp

SSCommitted

Points: 1765
More actions
October 7, 2008 at 6:02 am

#194094

i wanna do a process on last record inserted in a table.
is this a correct way ? :
CREATE TRIGGER pbxTime ON CallRecorder
FOR Insert
AS
DECLARE @RecId INT;
SELECT
@RecId=MAX(RecId) FROM CallRecorder ; --last inserted record id
UPDATE
CallRecorder
SET BeginTime='sth' where RecId=@Id; --update last recor

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

dr_csharp SSCommitted Points: 1765 More actions · Answer 1

Jack Corbett (10/7/2008)
While this trigger would work it may not be the best way to handle the situation. First you have to define what you mean by last row inserted. If I do a set based insert like:
Insert Into dbo.names
(
name
)
Select
name

i wanna do some process on last record inserted in Table.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

While this trigger would work it may not be the best way to handle the situation. First you have to define what you mean by last row inserted. If I do a set based insert like:

Insert Into dbo.names

(

name

)

Select

first_name + ' ' + last_name as name

From

dbo.persons

I don't know the order the names will be inserted.

Typically in a trigger you use the virtual inserted and deleted (updates and deletes) to manipulate the data. So I'd code the trigger something like this:

Update CallRecorder

Set BeginTime = 'sth'

Where

RecId = (Select Max(RecId) From inserted)

Edit: Sorry I accidentally tabbed and submitted my post BEFORE completing it:ermm:

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

dr_csharp SSCommitted Points: 1765 More actions · Answer 3

thanks jack . i do this :

CREATE TRIGGER test ON CallRecorder

FOR INSERT

AS

DECLARE @RecId INT;

DECLARE @BeginTime NVARCHAR(8);

SELECT @RecId=RecId,@BeginTime=BeginTime FROM Inserted

set @BeginTime=substring(@BeginTime,1,3);

Update CallRecorder

Set BeginTime =@BeginTime

Where

RecId = @RecId

i think everything is the best now 🙂

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Doc,

The issue with the trigger you propose is that it assumes that every insert is a single row insert. This is a common mistake made in triggers. Triggers fire on batches or sets so if you have 3 rows in the inserted table, your trigger will likely not do what you expect. You might be able to get away with your trigger by adding "Order BY RecID desc" to the query of the inserted table.

In order to guarantee that you will only update the "last" record inserted you need to use Max(RecId) somewhere in the trigger.

I also have a question about BeginTime. What are you storing in there? When I see a column that includes the word Time or Date I assume that it is a DateTime or SmallDateTime data type. If this is not what is in the column I would suggest naming the column appropriately and, if this is not possible, keeping this in mind for future projects.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

dr_csharp (10/7/2008)
thanks jack . i do this :
CREATE TRIGGER test ON CallRecorder
FOR INSERT
AS
DECLARE @RecId INT;
DECLARE @BeginTime NVARCHAR(8);
SELECT @RecId=RecId,@BeginTime=BeginTime FROM Inserted
set @BeginTime=substring(@BeginTime,1,3);
Update CallRecorder
Set BeginTime =@BeginTime
Where
RecId = @RecId
i think everything is the best now 🙂

Heh... Jack has been trying to quietly explain why that's a bad idea... I'll be a bit more direct... 😛

No, NO, NO, [font="Arial Black"]NO[/font] :hehe:! That's a RBAR trigger and if you ever insert more than 1 row for ANY reason, the trigger will only process the last row! If, for example, you insert 5 rows, for ANY reason, the first 4 will NOT be processed by the trigger!

You must write these types of triggers to use the INSERTED triggrer table to figure out which rows have been inserted.

--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)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

... and, if you write it correctly, it will also do just one "record" correctly, as well.

--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)

dr_csharp SSCommitted Points: 1765 More actions · Answer 7

Jeff Moden (10/7/2008)
dr_csharp (10/7/2008)
thanks jack . i do this :
CREATE TRIGGER test ON CallRecorder
FOR INSERT
AS
DECLARE @RecId INT;
DECLARE @BeginTime NVARCHAR(8);
SELECT @RecId=RecId,@BeginTime=BeginTime FROM Inserted
set @BeginTime=substring(@BeginTime,1,3);
Update CallRecorder
Set BeginTime =@BeginTime
Where
RecId = @RecId
i think everything is the best now 🙂
Heh... Jack has been trying to quietly explain why that's a bad idea... I'll be a bit more direct... 😛
No, NO, NO, [font="Arial Black"]NO[/font] :hehe:! That's a RBAR trigger and if you ever insert more than 1 row for ANY reason, the trigger will only process the last row! If, for example, you insert 5 rows, for ANY reason, the first 4 will NOT be processed by the trigger!
You must write these types of triggers to use the INSERTED triggrer table to figure out which rows have been inserted.

thanks jeff,im sure that one record insert each time..but ill appreciate you if guide me to a way that is correct if more than 1 record inserted ! thanks again )

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

dr_csharp (10/7/2008)
Jeff Moden (10/7/2008)
dr_csharp (10/7/2008)
thanks jack . i do this :
CREATE TRIGGER test ON CallRecorder
FOR INSERT
AS
DECLARE @RecId INT;
DECLARE @BeginTime NVARCHAR(8);
SELECT @RecId=RecId,@BeginTime=BeginTime FROM Inserted
set @BeginTime=substring(@BeginTime,1,3);
Update CallRecorder
Set BeginTime =@BeginTime
Where
RecId = @RecId
i think everything is the best now 🙂
Heh... Jack has been trying to quietly explain why that's a bad idea... I'll be a bit more direct... 😛
No, NO, NO, [font="Arial Black"]NO[/font] :hehe:! That's a RBAR trigger and if you ever insert more than 1 row for ANY reason, the trigger will only process the last row! If, for example, you insert 5 rows, for ANY reason, the first 4 will NOT be processed by the trigger!
You must write these types of triggers to use the INSERTED triggrer table to figure out which rows have been inserted.
thanks jeff,im sure that one record insert each time..but ill appreciate you if guide me to a way that is correct if more than 1 record inserted ! thanks again )

I did point you to a way that is correct if more than 1 row is inserted. The update I included in my first post should give you an idea of what you need to do in order to update the correct row. Granted this was based on your first post where you are updating using a constant. You do need to make some changes to get it to work by using the BeginTime in the inserted table, but my suggestion(s) do point you in the right direction. I could write the code for you, but then what would you learn?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

I agree with Jack... the best way for you to learn this one is to try it on your own, first. Read about "Triggers" and the "INSERTED/DELETED" tables that every trigger makes in Books Online...

--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)

Sergiy SSC Guru Points: 110208 More actions · Answer 10

Guys, I believe it's a good case when simple example would worth more than hours of reading.

Now for OP.

This is how your trigger FOR INSERT should look like:

Update CallRecorder

Set BeginTime = substring(BeginTime,1,3)

WHERE EXISTS (select 1 from inserted i

Where i.RecId = CallRecorder.RecId

This trigger will update every record in table CallRecorder affected by last insert.

And will update only affected records - WHERE EXISTS takes care of it.

_____________
Code for TallyGenerator

Jack Corbett SSC Guru Points: 184394 More actions · Answer 11

Sergiy (10/7/2008)
Guys, I believe it's a good case when simple example would worth more than hours of reading.
Now for OP.
This is how your trigger FOR INSERT should look like:
Update CallRecorder
Set BeginTime = substring(BeginTime,1,3)
WHERE EXISTS (select 1 from inserted i
Where i.RecId = CallRecorder.RecId
This trigger will update every record in table CallRecorder affected by last insert.
And will update only affected records - WHERE EXISTS takes care of it.

Sergiy,

I did provide an example in my first post that the OP should have been able to alter to do what they needed.

Also the code you provided doesn't meet the OP's request anymore than mine did as the OP only wants to update the BeginTime for the last row inserted. So the OP STILL needs to do some of the work.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

Actually, why does this need to be done in a trigger? This sounds like a process specific to a particular proc and should probably on be done from a proc. And, unless all the rows are updated with the datetime, I haven't seen anything to preserve the "batch" order.

--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)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 13

If we knew more about the process that requires this action to occur, we might be able to help better. It's difficult to imagine a call recorder that would mark the begin time just for one record. If a begin time needs to be added for each row inserted, a simple column default would do the trick with no fuss at all.

--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)

Sergiy SSC Guru Points: 110208 More actions · Answer 14

Jeff Moden (10/8/2008)
Actually, why does this need to be done in a trigger? This sounds like a process specific to a particular proc and should probably on be done from a proc.

Actually such conversions need to be done not even in database but in data entry points: textbox on UI or data import interface.

_____________
Code for TallyGenerator