Another trigger problem!

Question

Post reply

Another trigger problem!

igngua

SSCrazy

Points: 2510
More actions
September 2, 2009 at 2:20 pm

#214081

Hi!
I dont know why this trigger aint working.
Scenario:
Data gets inserted, trigger gets fired, i take all the data from INSERTED and then inserted back in the same table with a new value.
this is what i´ve got.
create trigger one on dbo.pedidos
after insert as
begin
set nocount on;
INSERT INTO dbo.pedidos
([PedNum]
,[CotNum]
,[SCodAux]
,[PedContacto]
,[PedComuID]
,[PedComuNom]
,[PedCiuID]
,[PedCiuNom]
,[PedVenID]
,[PedVenNom]
,[PedFechaCot]
,[PedGlosaCot]
,[PedFechaSolEje]
,[PedOrdCompra]
,[PedContrato]
,[PedObs]
,[ESTID])
select
(inserted.PedNum,
inserted.[CotNum],
inserted.[SCodAux],
inserted.[PedContacto],
inserted.[PedComuID],
inserted.[PedComuNom],
inserted.[PedCiuID],
inserted.[PedCiuNom],
inserted.[PedVenID],
inserted.[PedVenNom],
inserted.[PedFechaCot],
inserted.[PedGlosaCot],
inserted.[PedFechaSolEje],
inserted.[PedOrdCompra],
inserted.[PedContrato],
inserted.[PedObs],
'2')
end

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

igngua (9/2/2009)
I dont know why this trigger aint working.

What do you mean by 'not working'? What's it not doing that it should be?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lamprey13 SSCertifiable Points: 6190 More actions · Answer 2

Is thr FROM clause ommited or missing?

igngua (9/2/2009)
Hi!
I dont know why this trigger aint working.
Scenario:
Data gets inserted, trigger gets fired, i take all the data from INSERTED and then inserted back in the same table with a new value.
this is what i´ve got.
create trigger one on dbo.pedidos
after insert as
begin
set nocount on;
INSERT INTO dbo.pedidos
([PedNum]
,[CotNum]
,[SCodAux]
,[PedContacto]
,[PedComuID]
,[PedComuNom]
,[PedCiuID]
,[PedCiuNom]
,[PedVenID]
,[PedVenNom]
,[PedFechaCot]
,[PedGlosaCot]
,[PedFechaSolEje]
,[PedOrdCompra]
,[PedContrato]
,[PedObs]
,[ESTID])
select
(inserted.PedNum,
inserted.[CotNum],
inserted.[SCodAux],
inserted.[PedContacto],
inserted.[PedComuID],
inserted.[PedComuNom],
inserted.[PedCiuID],
inserted.[PedCiuNom],
inserted.[PedVenID],
inserted.[PedVenNom],
inserted.[PedFechaCot],
inserted.[PedGlosaCot],
inserted.[PedFechaSolEje],
inserted.[PedOrdCompra],
inserted.[PedContrato],
inserted.[PedObs],
'2')
FROM Inserted
end

igngua SSCrazy Points: 2510 More actions · Answer 3

it says syntax error.

create trigger one on dbo.pedidos

after insert as

begin

set nocount on;

INSERT INTO dbo.pedidos(PedNum,ESTID)

select(inserted.PedNum,'2')

from inserted

end

dont know what i´m missing.

igngua SSCrazy Points: 2510 More actions · Answer 4

thanks!! i corrected it, it didnt work also...it i´m gonna do it all over again.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

igngua (9/2/2009)
it says syntax error.

Brackets around the select shouldn't be there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Shortened up the list of columns but this select works, just addin all the column names that I skipped over to make testing quick and easier.

INSERT INTO dbo.pedidos ([PedNum],[ESTID])

select PedNum, '2' FROM inserted

end

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

SQLRNNR SSC Guru Points: 281324 More actions · Answer 7

Go ahead and remove the paretheses from your select clause

instead of

select

(inserted.PedNum,

...

inserted.[PedObs],

'2')

select

inserted.PedNum,

...

inserted.[PedObs],

'2'

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

igngua SSCrazy Points: 2510 More actions · Answer 8

thanks for the help guys.

Now another thing...

the triggers does what is supposed to...but one of the rows is PK, so the trigger cant insert the data from inserted in the original table because its already there.

Is there anyway i can do this, keeping the row as a PK?

here is the working trigger.

create trigger insertaestadopendiente on dbo.pedidos

after insert as

begin

set nocount on;

INSERT INTO dbo.pedidos

[PedNum] --this is PK--

,[ESTID])

select

inserted.PedNum,

'2'

from inserted

end

thanks in advance.