January 13, 2012 at 1:09 pm
create trigger jam_spu_csp_insert
on customer_special_prices
for insert
as
referencing new as new_name for each row
begin
insert into jam_spu ( customer_id, location_number, seq_num )
values ( new_name.customer_id, new_name.location_number, new_name.seq_num )
end
go
error I am getting
Server: Msg 170, Level 15, State 1, Procedure jam_spu_im_changed, Line 3
Line 3: Incorrect syntax near 'after'.
Server: Msg 128, Level 15, State 1, Procedure jam_spu_im_changed, Line 10
The name 'seq_num' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
January 13, 2012 at 1:28 pm
venu9099 (1/13/2012)
create trigger jam_spu_csp_inserton customer_special_prices
for insert
as
referencing new as new_name for each row
begin
insert into jam_spu ( customer_id, location_number, seq_num )
values ( new_name.customer_id, new_name.location_number, new_name.seq_num )
end
go
error I am getting
Server: Msg 170, Level 15, State 1, Procedure jam_spu_im_changed, Line 3
Line 3: Incorrect syntax near 'after'.
Server: Msg 128, Level 15, State 1, Procedure jam_spu_im_changed, Line 10
The name 'seq_num' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
I'm guessing you might want to doublecheck that you're posting the accurate text, as the error message refers to "jam_spu_im_changed" but I don't see that name listed in any other text of your post. I could be wrong tho, not a big trigger writer myself! Good luck in any case!
January 13, 2012 at 1:38 pm
Create Trigger jam_spu_csp_insert
on dbo.customer_special_prices
After Insert
AS
Begin
Set nocount on;
insert into jam_spu ( customer_id, location_number, seq_num )
values ( new_name.customer_id, new_name.location_number, new_name.seq_num )
end
go
error
Server: Msg 128, Level 15, State 1, Procedure jam_spu_csp_insert, Line 8
The name 'customer_id' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
January 13, 2012 at 1:47 pm
It looks like you're trying to write this trigger Oracle-style, where the trigger runs once for each row in the transaction. SQL Server doesn't fire triggers that way.
Try this:
create trigger jam_spu_csp_insert
on customer_special_prices
for insert
as
begin
insert into jam_spu ( customer_id, location_number, seq_num )
select customer_id, location_number, seq_num
from inserted -- SQL Server "pseudo-table" that contains the new data inserted/updated by the transaction
end
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2012 at 1:48 pm
venu9099 (1/13/2012)
Create Trigger jam_spu_csp_inserton dbo.customer_special_prices
After Insert
AS
Begin
Set nocount on;
insert into jam_spu ( customer_id, location_number, seq_num )
values ( new_name.customer_id, new_name.location_number, new_name.seq_num )
end
go
error
Server: Msg 128, Level 15, State 1, Procedure jam_spu_csp_insert, Line 8
The name 'customer_id' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
instead of using the insert like:
insert into jam_spu ( customer_id, location_number, seq_num )
values ( new_name.customer_id, new_name.location_number, new_name.seq_num )
You'll want to provide a select clause from the logical table "inserted" that is a logical table that materializes in the context of an insert trigger:
insert into jam_spu ( customer_id, location_number, seq_num )
select customer_id, location_number, seq_num
from inserted
the "values" clause expects something other than column names as typically you use the "values" clause with constants.
As alwayss TEST TEST TEST!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply