When is "inserted" available for use? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

When is "inserted" available for use?

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-09-26 : 13:43:29

oops, may try agin?

each insert is a singularly event

THIS dosen't work

ALTER TRIGGER co_MailOrder
ON notifier
FOR INSERT
AS
If EXISTS(SELECT i.mailid from inserted i)
BEGIN
SELECT
A.orderid, A.sono, A.startdate, A.userxxx,
B.itemid, B.carbongrade, B.lengthxxx,
B.widthxxx, B.heightxxx, B.typexxx, B.piececount
INTO #tempAB
FROM
orders A
INNER JOIN orderitems B
ON A.orderid=B.orderid WHERE A.orderid = (SELECT j.orderid from inserted j)

.....

THIS DOES WORK

ALTER TRIGGER co_MailOrder
ON notifier
FOR INSERT
AS
If EXISTS(SELECT i.mailid from inserted i)
BEGIN
SELECT
A.orderid, A.sono, A.startdate, A.userxxx,
B.itemid, B.carbongrade, B.lengthxxx,
B.widthxxx, B.heightxxx, B.typexxx, B.piececount
INTO #tempAB
FROM
orders A
INNER JOIN orderitems B
ON A.orderid=B.orderid WHERE A.orderid = 38

......

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-26 : 13:57:19

Why don't you just join to inserted as well? Does this work:

INNER JOIN orderitems B
ON a.orderid = b.orderid
INNER JOIN inserted i
ON a.orderid = i.orderid

Tara

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-09-26 : 14:10:30

Tried that, no go.

The one I showed as working 'WHERE A.orderid = 38'
is an already existing ID not they one generated
on the current insert.

I did that to prove to myself that the
SELECT.....into #tempAB would work inside the trigger but I think
the "SELECT i.mailid from inserted i" isn't available when that table gets built.

SQL 7.0 by the way

Thanks

Voted best SQL forum nickname...."Tutorial-D"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-26 : 14:15:58

EDIT: removed my post here cuz I rethought it and now I realize it was wrong.

Tara

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-09-26 : 14:35:18

Tried MAX(), that will give a single value but that also dosen't work.
Seems it is just not there, but only WRT to temp table creation.
SELECT i.mailid from inserted i.
works fine in other areas of the trigger.
I'll try and model what I'm doing post it.

Voted best SQL forum nickname...."Tutorial-D"

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-09-26 : 15:53:40

Sorry Tara this was a wild goose chase,
thanks though you help alot.

Voted best SQL forum nickname...."Tutorial-D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources