Hey so we have created this trigger. It doesnt work and provides these error messages:
create or replace trigger create_user
BEFORE INSERT OR UPDATE
FOR EACH ROW
SELECT SUBSTR(FIRST_NAME, 1, 1) || SUBSTR(surname, 1,3)
WHERE STAFF_ID > 9999
ORDER BY SURNAME;
2/2 PL/SQL: SQL Statement ignored
3/7 PLS-00201: identifier 'USERNAME' must be declared
4/2 PL/SQL: ORA-00904: : invalid identifier
We have a username table and EA_MARKETING table created.
Old and New versions of the row need to be referenced. Since this is INSERT or UPDATE, you likely want to manipulate the NEW data.
:new.username=SUBSTR(:new.FIRST_NAME, 1, 1) || SUBSTR(:new.surname, 1,3)
If that select returns more than one row it will fail. Since it is selecting from the table it is changing it is likely to get a mutating table error.
Does username depend on data in the row being updated or in data in some other row?
You say username is a table, but you are referencing it like a PL/SQL variable. What is the task you need to accomplish? I think there's something more behind this question.
As I look back at this, I wonder if you are used to working in SQL Server?
SELECT SUBSTR(FIRST_NAME, 1, 1) || SUBSTR(surname, 1,3) INTO USERNAME FROM EA_MARKETING WHERE STAFF_ID > 9999 ORDER BY SURNAME;
is SQL Server syntax for appending the results of a query to an existing table. Is that what you are attempting?
The equivalent Oracle syntax would be:
insert into username (some_column) SELECT SUBSTR(FIRST_NAME, 1, 1) || SUBSTR(surname, 1,3) FROM EA_MARKETING WHERE STAFF_ID > 9999 ORDER BY SURNAME;
That's the syntax, now looking at the logic of that instruction:
You are proposing a trigger which fires for every insert or update.
If you have just one row in EA_MARKETING where staff_id>9999 then each update or insert of any row in EA_MARKETING will insert this name into USERNAME. If there is a unique constraint on the column you insert into in username, this will work once then fail with each subsequent attempt.
If you have 100 rows in EA_MARKETING where staff_id>9999 and insert or update 10 rows in a statement (regardless of staff_id) you will insert those 100 rows 10 times, adding 1000 USERNAME rows that already exist. If there is a unique constraint on the column you insert into in username, this fails.
A trigger cannot access other rows in the table being triggered, so Oracle happens to prevent you from this. Inserts may work fine, but updates are very likely to throw ORA-04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
Message was edited by: Brian Bontrager (removed hasty analysis, thinking this was updating the table being modified) (updated analysis of whats going on)