trigger 1

[anniez]

I don't know why I'm having so much trouble with this!

When fieldA gets changed to "0"
I want fieldB and fieldC to get changed to "000"


if :new.fielda="0"
then
update table.fieldA="000"
fieldB="000"
end if;

 

[carp]

Try

if :new.fielda="0"
then
update my_table
SET fieldA="000",
fieldB="000";
end if;

 

[carp]

However, if you are trying to update a row on the same table that the trigger is on, then you can't do the above. Instead, try

if :new.fielda='0'
then
:new.fieldA='000';
:new.fieldB='000';
end if;

Also, I just noticed you are using double quotes. Try single quotes instead.

 

[SantaMufasa]

Anniez,

In addition to Carp's excellent observations, both of your fields, "FieldA" and "FieldB", must be defined as some form of character (CHAR, VARCHAR, VARCHAR2) for the logic to have any effect. If either of the fields are some form of NUMBER, then your logic causes no appreciable change, since storing '00000000000000' still stores only '0' in a NUMBER field.

Dave
Sandy, Utah, USA @ 21:13 GMT, 14:13 Mountain Time

 

[anniez]

I still get ora-04079 error:


create or replace trigger trigger_name
after update of fieldA
on abcd.tablename
if :new.fielda='0'
then
:new.fieldb='000';
:new.fieldc='000';
end if;

 

[SantaMufasa]

Anniez,

...What we have learned from this thread: "Always include all of your code; not just an excerpt."

The problem is that you can code "ld." and ":new." specifiers ONLY when your trigger uses the "FOR EACH ROW" clause:

create or replace trigger trigger_name
after update of fieldA
on abcd.tablename
FOR EACH ROW -- necessary addition
if :new.fielda='0'
then
:new.fieldb='000';
:new.fieldc='000';
end if;
/

Add that and let us know.

Dave
Sandy, Utah, USA @ 21:27 GMT, 14:27 Mountain Time

 

[SantaMufasa]

Anniez,

...And for the sake of precision, I should have included all the code you would need so that you can copy and paste for success:

create or replace trigger trigger_name
after update of fieldA 
on abcd.tablename
FOR EACH ROW -- necessary addition
BEGIN        -- Syntax missing from earlier replies
if :new.fielda='0'
then
   :new.fieldb='000';
   :new.fieldc='000';
end if;
END;         -- Syntax missing from earlier replies
/

Dave
Sandy, Utah, USA @ 22:07 GMT, 15:07 Mountain Time

 

[carp]

Dave -
"replies"?
Haven't seen that one before - what does it do?

 

[SantaMufasa]

Carp,

First, I believe I speak for all of our Oracle 8i/9i Tek-Tips Forum buddies when I say, "Welcome back from your multi-week hiatus from Tek-Tips." (I wonder where Sybaseguru has been, as well.)

Secondly, my apologies for shortening my code comments from a longer, more precice, "-- Syntax missing from earlier Tek-Tips replies to this thread". Did that clarify my use of the term "replies"?

Dave
Sandy, Utah, USA @ 22:55 GMT, 15:55 Mountain Time

 

[carp]

Sadly, yes.
I had hoped there was a new trick to be learned here!

 

[SantaMufasa]

Carp,

What 'new tricks' could I EVER teach you, my buddy?

Dave

 

[anniez]

Am I missing something? Now I get PLS 00103: encountered the symbol"=" shen expecting one of the following.....

CREATE OR REPLACE TRIGGER ABCD.TRIGGERNAME
AFTER UPDATE OF FIELDA
ON ABCD.TABLENAME

REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
IF :NEW.FIELDA='0'
THEN
:NEW.FIELDB='000';
:NEW.FIELDC='000';
END IF;
END;

Thanks

 

[SantaMufasa]

Yes, AnnieZ, you and Carp, and I all missed something in Carp's suggested code: in PL/SQL, an assignment occurs with a ":=" symbol set, not "=". When assinging values in PL/SQL (apart from SQL "UPDATE" assignments), use ":=". After you make those changes, your results should improve.

Dave
Sandy, Utah, USA @ 17:21 GMT, 10:21 Mountain Time

 

[anniez]

I hope this is my last message...
I really don't know where the := goes.
IF :NEW.FIELDA :='0'
THEN
:NEW.FIELDB :='000';
:NEW.FIELDC :='000';
END IF;
???????
Sorry for being so dense about this.

 

[SantaMufasa]

AnnieZ,

Don't worry about density...If this is the first time doing something, we all deserve a little slack since none of us are born with a full set of syntax.

And, yes, your most recent code assertion looks syntactically correct.

Send us one more post telling us it worked and giving Carp a purple star.

Dave
Sandy, Utah, USA @ 19:30 GMT, 12:30 Mountain Time

 

[BJCooperIT]

Sorry to diagree Dave, but shouldn't it be:

IF  :NEW.FIELDA = '0' THEN     -- no :=
    :NEW.FIELDB := '000';
    :NEW.FIELDC := '000';
END IF;

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[SantaMufasa]

BJ,

You have every right (and responsibility) to take issue since I overlooked Annie's incorrect ":=" usage in the condition check, focusing instead upon her assignment syntax. Good catch ! Feel free to "diagree" anytime (especially when I'm wrong)

Dave
Sandy, Utah, USA @ 19:52 GMT, 12:52 Mountain Time

 

[anniez]

ok - I've spent way too much time on this - as well as all of you. Maybe I'm on the wrong track. I still get an error.

When someone leaves, we change their status to '0'
I want their dept and bldg each set to '000' so the user doesn't have to do it. It's only done on one record - in the same table.
Is this trigger what I should be doing? Is it my syntax?
Thanks

 

[carp]

Anniez -
Let's try one more post with your full trigger syntax:

CREATE OR REPLACE TRIGGER ABCD.TRIGGERNAME
AFTER UPDATE OF FIELDA
ON ABCD.TABLENAME
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
IF :NEW.FIELDA = '0'
THEN
:NEW.FIELDB := '000';
:NEW.FIELDC := '000';
END IF;
END;

If this compiles but doesn't work, let us know what the symptoms are. One thing that comes to mind is whether or not you are querying your results from the same session you are updating the table from? If not, does your updating session issue a commit before you query from the other session?

 

[SantaMufasa]

Annie,

When you say, "I still get an error," what is the error; can you please copy/paste your code and the error text. We typically stay with a problem until it's resolved, so don't worry about us.

Dave
Sandy, Utah, USA @ 20:28 GMT, 13:28 Mountain Time