@@identity and insert trigger - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

@@identity and insert trigger

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2003-08-18 : 16:15:17

Hi Everyone -

I would like to put a trigger on an insert event in a table -

The trigger will need to update another table with the identity and a couple of other fields from the inserted record...

question is....

how do i retrieve the @@identity (and the other fields) on the freshly inserted row ???

thanks
tony

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-18 : 16:30:06

Triggers in SQL Server are set-based: they fire once for each DML statement, not row. So an INSERT of 100 rows, for example, causes the trigger to fire only once instead of 100 times. @@identity therefore has no logical meaning inside a trigger.

Read about the special inserted and deleted tables available to you in triggers in BOL.

Jonathan
{0}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-18 : 16:35:56

Instead of using @@IDENTITY, just use the INSERTED table as Jonathan mentioned.


CREATE TABLE SomeTable
(
SomeTable_ID INT IDENTITY(1, 1) NOT NULL,
SomeColumn VARCHAR(50) NOT NULL,
AnotherColumn VARCHAR(50) NOT NULL
)

CREATE TABLE SomeOtherTable
(
SomeOtherTable_ID INT IDENTITY(1, 1) NOT NULL,
SomeTable_ID INT NOT NULL,
SomeColumn VARCHAR(50) NOT NULL,
AnotherColumn VARCHAR(50) NOT NULL
)

CREATE TRIGGER trig_SomeTrigger 
ON SomeTable 
FOR INSERT AS

      INSERT INTO SomeOtherTable (SomeTable_ID, SomeColumn, AnotherColumn)
      SELECT SomeTable_ID, SomeColumn, AnotherColumn
      FROM inserted ins
GO

INSERT INTO SomeTable (SomeColumn, AnotherColumn)
SELECT 'Tara', 'SQL Warrior Princess'

INSERT INTO SomeTable (SomeColumn, AnotherColumn)
SELECT 'Me', 'Woohoo!'

INSERT INTO SomeTable (SomeColumn, AnotherColumn)
SELECT 'SQLTeam', 'Rules'

SELECT * 
FROM SomeTable

SELECT * 
FROM SomeOtherTable

DROP TRIGGER trig_SomeTrigger
DROP TABLE SomeTable
DROP TABLE SomeOtherTable

Tara

pithhelmet
Posting Yak Master

183 Posts

Posted - 2003-08-18 : 17:10:03

Comin close....

this is what i have....

CREATE TRIGGER TR_Update_Broadcast_Queue
ON [dbo].[broadcast_message_table]
AFTER INSERT
AS
If Exists (Select 1 From Inserted)
BEGIN
insert into broadcast_queue_table
(
unit_id,
sent_message_id,
time_to_live
)
values
(
(select inserted.receiver_id from inserted),
(select inserted.sent_message_id from inserted),
'1/1/2002'
)

END

But alas -
I receive a error 1046 - subqueries not allowed...

thanks for the help!!!

take care
tony

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-18 : 17:30:00

Instead of:

CREATE TRIGGER TR_Update_Broadcast_Queue
ON [dbo].[broadcast_message_table]
AFTER INSERT
AS
If Exists (Select 1 From Inserted)
BEGIN
insert into broadcast_queue_table
(
unit_id,
sent_message_id,
time_to_live
)
values
(
(select inserted.receiver_id from inserted),
(select inserted.sent_message_id from inserted),
'1/1/2002'
)

END

Try this:

CREATE TRIGGER TR_Update_Broadcast_Queue
ON [dbo].[broadcast_message_table]
AFTER INSERT
AS

INSERT INTO broadcast_queue_table (unit_id,sent_message_id,time_to_live)
SELECT receiver_id, sent_message_id, '1/1/2002'
FROM inserted

Tara

pithhelmet
Posting Yak Master

183 Posts

Posted - 2003-08-18 : 17:42:41

Hi Folks - (and Tara)

Fantastic!!!

that last code changed worked fine!!!

Now - a little more tricky question...

the date field....

1/1/2002

I would like to set that to the current datetime
plus 10 minutes....

This is what i'm trying....

declare @new_date as datetime
@new_date = select dateadd(minute, 10, getdate())

but its kicking me to the curb....

any ideas???

thanks again for all your help!!!!

take care
tony

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-18 : 17:47:02

CREATE TRIGGER TR_Update_Broadcast_Queue
ON [dbo].[broadcast_message_table]
AFTER INSERT
AS

INSERT INTO broadcast_queue_table (unit_id,sent_message_id,time_to_live)
SELECT receiver_id, sent_message_id, DATEADD(minute, 10, getdate())
FROM inserted

Tara

pithhelmet
Posting Yak Master

183 Posts

Posted - 2003-08-18 : 17:50:26

100% RIGHT ON THE MOONEY!!!

Tara - you get a cookie!!!

thanks a million

take care
tony

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources