Home » SQL & PL/SQL » SQL & PL/SQL » update statement with :new.column throwing error
update statement with :new.column throwing error [message #423595] Fri, 25 September 2009 08:04 Go to next message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
Hi

I have an update statement in my trigger.
I tried doing the following:

update TABLE1 set :NEW.COLUMN1 = 'VALUE'

This shows an error stating an 'INVALID COLUMN SPECIFICATION'

Is there a way to set the column as :NEW?

Thanks
Re: update statement with :new.column throwing error [message #423597 is a reply to message #423595] Fri, 25 September 2009 08:36 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Just

:NEW.COLUMN1 := 'VALUE';


At least if the statement is in the place I now guess it is, since your post is again void of any real information.

[Updated on: Fri, 25 September 2009 08:36]

Report message to a moderator

Re: update statement with :new.column throwing error [message #423599 is a reply to message #423595] Fri, 25 September 2009 08:43 Go to previous messageGo to next message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
OK
The suggestion you made above didnt work. Thanks anyway.
Here is the brief layout of the trigger:


create or replace
TRIGGER "TALLYMANBMWNEW".bmw_upd_acchostchar
AFTER UPDATE OF FIELD1
on table2
--REFERENCING OLD AS OLD NEW AS NEW
for each row

IF :NEW.value2 = 'Y' THEN
INSERT INTO table1
(column1
)
VALUES
(value
);
UPDATE TABLE2 SET :NEW.VALUE2:= NULL;
END IF ;

END;

Is the update statement in the correct place?

thanks again.

Re: update statement with :new.column throwing error [message #423600 is a reply to message #423599] Fri, 25 September 2009 08:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You should not do an update; you should do as ThomasG shows you: set the variable to the new value.

[Updated on: Fri, 25 September 2009 08:47]

Report message to a moderator

Re: update statement with :new.column throwing error [message #423604 is a reply to message #423600] Fri, 25 September 2009 09:02 Go to previous messageGo to next message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
Ah ok my mistake. Whilst I was waiting for a reply I found a different way of getting it to work:

create or replace
TRIGGER "TALLYMANBMWNEW".bmw_upd_acchostchar
AFTER UPDATE OF FIELD1
on table2
--REFERENCING OLD AS OLD NEW AS NEW
for each row

IF :[b]OLD[/b].value2 = 'Y' THEN
INSERT INTO table1
(column1
)
VALUES
(value
);
UPDATE TABLE2 SET VALUE2:= NULL;
END IF ;

END;


I used OLD instead of NEW in the IF statement.
Does this matter?

[Updated on: Fri, 25 September 2009 09:03]

Report message to a moderator

Re: update statement with :new.column throwing error [message #423605 is a reply to message #423604] Fri, 25 September 2009 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I used OLD instead of NEW in the IF statement.

Are OLD and NEW the same ones?

Regards
Michel
Re: update statement with :new.column throwing error [message #423606 is a reply to message #423595] Fri, 25 September 2009 09:10 Go to previous messageGo to next message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
No they are different, Ive just tested it and worked out that the update statement fires but the trigger doesnt insert a new row. nI need to keep that fi statement value as NEW.
I tried setting the variable as suggested but I get the error:

'cannot change new values for this trigger type'

Basically the trigger is running when a field i updated. Once the trigger is ran I need to reset this value back to null. But its hving a problem because of the type of trigger?
Is there any way around this?
I didnt come up with this trigger just to be clear, it has been trequested!
Re: update statement with :new.column throwing error [message #423608 is a reply to message #423606] Fri, 25 September 2009 09:18 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Change the trigger type to before update. Then you can change the values using :NEW
Re: update statement with :new.column throwing error [message #423632 is a reply to message #423604] Fri, 25 September 2009 12:49 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
GuestUser32 wrote on Fri, 25 September 2009 10:02

create or replace
TRIGGER "TALLYMANBMWNEW".bmw_upd_acchostchar
AFTER UPDATE OF FIELD1
on table2
--REFERENCING OLD AS OLD NEW AS NEW
for each row

IF :[b]OLD[/b].value2 = 'Y' THEN
INSERT INTO table1
(column1
)
VALUES
(value
);
UPDATE TABLE2 SET VALUE2:= NULL;
END IF ;

END;



You do realize that you are updating EVERY ROW in the table with this trigger and that this trigger will fail to compile as 'value' is not valid in your INSERT statement, right?
This is not to sound harsh, but I think a little understanding of triggers and simple logic would be helpful.

[Updated on: Fri, 25 September 2009 12:52]

Report message to a moderator

Previous Topic: kill user sessions for certain user
Next Topic: Invalid record error with triggers
Goto Forum:
  


Current Time: Wed Dec 07 18:25:23 CST 2016

Total time taken to generate the page: 0.11458 seconds