0 
create trigger Trigger_1
    Before INSERT
    on Adresse
    for each row
    declare
        pruefen int;
begin
    select COUNT(*) 
    into pruefen 
    from CITY 
    where city.NAME = :new.stadt 
    and CITY.PROVINCE = :new.provinz;
    if(pruefen = 0) then
        RAISE_APPLICATION_ERROR(-20000, 'Fehler');
    end if;
end;

Error:

[2020-01-18 10:14:12] [72000][20000] ORA-20000: Fehler
[2020-01-18 10:14:12] ORA-06512: in "ZOGL1011.TRIGGER_1", Row 6
[2020-01-18 10:14:12] ORA-04088: error during execution of Trigger "ZOGL1011.TRIGGER_1"
[2020-01-18 10:14:12] Position: 12

I don't get the problem.. :new.stadt and :new.provinz are in red, but I can create the trigger.

Thank you for your help!

Improve this question
3
  • its an execution issue, if i insert something... Commented Jan 18, 2020 at 9:25
  • Is data types of the :new.stadt and city.name same? Also, Is data type of the :new.provinz and city.province same? Commented Jan 18, 2020 at 9:29
  • yes the datatyp of those are the same Commented Jan 18, 2020 at 9:31

1 Answer 1

Reset to default
3

[2020-01-18 10:14:12] [72000][20000] ORA-20000: Fehler

That is the error your trigger raises when there is no data in CITY which matches the record you're trying to insert into ADRESSE. In other words it is your trigger working as designed. So if this outcome surprises you you need to check the data you're using for both CITY and ADRESSE.

Just to prove it, here is a demo on db<>fiddle.

Incidentally, this seems like the sort of thing which is properly enforced through a foreign key. Using a trigger to enforce a relationship is wrong because:

  1. It performs worse and doesn't scale.
  2. It may fail in a multi-user environment.
  3. It won't continue to enforce the relationship when DML is executed on the referenced table (CITY in your case).
  4. In obfuscates the data model.
  5. Using non-standard approaches confuses other developers who have to work with or maintain your code.

Maybe you have a poor data model which won't allow you to use a foreign key here? Perhaps CITY is missing a unique key of (state, province).

Although probably this is just the assignment. It is the fate of triggers to be used in homework exercises in ways in which they should never be used in real life.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.