Ora 8i - Trigger - Referencing????

[Guest_imported]

Hallo All!
I tried to create a trigger as following:

CREATE TRIGGER "SHC"."R003AULIDAUS_INSERT" AFTER UPDATE OF "STAT" ON "T082AULI"

REFERENCING OLD AS <>8 NEW AS 8

FOR EACH ROW WHEN (T087POLI.STAT=8) BEGIN
IF UPDATING THEN
INSERT INTO R003AULIDAUS
(MANR, AINR, LAUF, STAT)
VALUES
T087POLI.MANR, :T087POLI.AINR, :T087POLI.LAUF, 1)
END IF;
END;

I know that referencing line is incorrect but idunno what to write in it?
If i leave it out, I get:
ORA-04076: ungültige Angabe für NEW oder OLD
i.e. invalid statement for NEW or OLD

if i leave it in, i get:
ORA-04074: invalid REFERENCING-Name

pls someone help me, its urgent
thx in advance,
Homer

 

[SpiritOfLennon]

CREATE TRIGGER &quot;SHC&quot;.&quot;R003AULIDAUS_INSERT&quot; AFTER UPDATE OF &quot;STAT&quot; ON &quot;T082AULI&quot;

REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW WHEN (NEW.T087POLI.STAT=8) BEGIN
IF UPDATING THEN
INSERT INTO R003AULIDAUS
(MANR, AINR, LAUF, STAT)
VALUES
NEW.T087POLI.MANR, :NEW.T087POLI.AINR, :NEW.T087POLI.LAUF, 1)
END IF;
END;

Should work. The old and new refer to the value before and after the update is carried out. If you still have problems let me know. SOL
The best thing about banging your head against a wall is when you stop.

 

[Guest_imported]

Dear SOL
Thank you very much,
but now i get:
ORA-00904: Invalid Fieldname

Sorry, i´m still newbie in this topic.

CREATE TRIGGER &quot;SHC&quot;.&quot;R003AULIDAUS_INSERT&quot; AFTER UPDATE OF &quot;STAT&quot; ON &quot;T082AULI&quot;

REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW WHEN (NEW.T087POLI.STAT=8) BEGIN
IF UPDATE THEN
INSERT INTO R003AULIDAUS
(MANR, AINR, LAUF, STAT)
VALUES
NEW.T087POLI.MANR, :NEW.T087POLI.AINR, :NEW.T087POLI.LAUF, 1)
END IF;
END;

and again, thx in advance

 

[SpiritOfLennon]

Oracle is generally right on this, you may have a typo. Okay in that case I need to see the schemas for R003AILIDAUS and T082AULI. You can get these using ;
describe R003AILIDAUS
for example SOL
The best thing about banging your head against a wall is when you stop.

 

[Guest_imported]

Here you are:

R003AULIDAUS:
Name Null? Typ
----------------------------------------- -------- -------------
MANR NOT NULL NUMBER(6)
AINR NOT NULL NUMBER(8)
LAUF NOT NULL NUMBER(2)
STAT NOT NULL VARCHAR2(2)

T087POLI:
Name Null? Typ
----------------------------------------- -------- -------------
RECNUM NOT NULL NUMBER(8)
MANR NOT NULL NUMBER(6)
AINR NOT NULL NUMBER(8)
BLNR NUMBER(8)
LAUF NOT NULL NUMBER(2)
AUFPOS NOT NULL NUMBER(4)
MENGE NUMBER(10,4)
STAT VARCHAR2(1)
FSTAT VARCHAR2(5)
KZPOS VARCHAR2(1)
DIFFVW NUMBER(8,2)
DIFFEW NUMBER(8,2)
SCAB VARCHAR2(1)
VKPR NUMBER(10,2)
RFREI VARCHAR2(2)
EIPR NUMBER(10,2)
TXTHW VARCHAR2(1)
RABA NUMBER(6,2)
KATR NUMBER(8)
GERE VARCHAR2(1)
GUTM NUMBER(10,4)
TEILNR VARCHAR2(2)
ARNR NOT NULL VARCHAR2(20)
KDNR NOT NULL VARCHAR2(8)
LWERT NUMBER(10,2)
KSTAT VARCHAR2(1)
TSTAT VARCHAR2(1)
BASPR NUMBER(10,2)
EPNR NUMBER(6)
FR5 DATE
REFERENZ_147 VARCHAR2(15)
DRUCKER VARCHAR2(4)

perhaps a bit confusing, but i hope you´ll get it.
need more info?
thx

 

[SpiritOfLennon]

You appear to have made a typo on first line.
CREATE TRIGGER &quot;SHC&quot;.&quot;R003AULIDAUS_INSERT&quot; AFTER UPDATE OF &quot;STAT&quot; ON &quot;T082AULI&quot;
should be
CREATE TRIGGER &quot;SHC&quot;.&quot;R003AULIDAUS_INSERT&quot; AFTER UPDATE OF &quot;STAT&quot; ON &quot;T087AULI&quot;.

If this does not work, let me know. SOL
The best thing about banging your head against a wall is when you stop.

 

[SpiritOfLennon]

No sorry, it should be CREATE TRIGGER &quot;SHC&quot;.&quot;R003AULIDAUS_INSERT&quot; AFTER UPDATE OF &quot;STAT&quot; ON &quot;T087POLI&quot;. SOL
The best thing about banging your head against a wall is when you stop.

 

[rcurva]

Remove the extra reference to your table T082AULI new.T082AULI.manr), do something like;

CREATE OR REPLACE
TRIGGER SHC.R003AULIDAUS_INSERT
AFTER UPDATE OF STAT ON T082AULI
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
WHEN (NEW.STAT=8)
BEGIN
IF UPDATE THEN
INSERT INTO R003AULIDAUS
(MANR, AINR, LAUF, STAT)
VALUES
NEW.MANR, :NEW.AINR, :NEW.LAUF, 1);
END IF;
END;
/

 

[SpiritOfLennon]

rcurva is right, you should remove these references. However I think you still have the typo if your two tables are calles R003AULIDAUS and T087POLI then your trigger should read.

CREATE OR REPLACE
TRIGGER SHC.R003AULIDAUS_INSERT
AFTER UPDATE OF STAT ON T087POLI
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
WHEN (NEW.STAT=8)
BEGIN
IF UPDATE THEN
INSERT INTO R003AULIDAUS
(MANR, AINR, LAUF, STAT)
VALUES
NEW.MANR, :NEW.AINR, :NEW.LAUF, 1);
END IF;
END;
/
SOL
The best thing about banging your head against a wall is when you stop.

 

[rcurva]

No, I think the logic is right. You intend to insert into table R003AULIDAUS when the table T082AULI column STAT is updated and has a value equal to 8, right?

But what troubles me is your naming convention. If you intend to create a trigger on table T082AULI, why is your trigger name R003AULIDAUS_INSERT ?

It's quite confusing, one thing is the table, and the dml statement is another (UPDATE). Ideally, your trigger name must be T082AULI_UPDATE.

 

[Guest_imported]

And thats the way , aha aha, I like it!

-> Everything works!

Great help rcurva! and of course thank you too, SOL.
this was my first visit here, and it won´t be my last.
See you soon!
(I think next time i´ll be a registered member in here!)
I never thought, that i will get my answers that fast.
cu,
;-)

 

[Guest_imported]

Rcurva!
you got me! Logic is right.

I chose the name, because the trigger R003AULIDAUS_INSERT
INSERTs data into table R003AULIDAUS.
did i get it wrong.
I already mentioned, NEWBIE-ALERT!!!! *gg*
have to go on working.
this project has to be finished tomorrow.
cu later,
Hucki

 

[SpiritOfLennon]

Conventionally, yes it is better to name triggers after the table they act upon, although it isn't mandatory, it just makes it easier to administer the database. SOL
The best thing about banging your head against a wall is when you stop.