Triggers or PL/SQL are not my usual forte, so hoping for some suggestions here.
For an application someone asked if we could have it so a certain field could only be forced to be set to a certain value for certain users.
Up until now this set of users do not have insert or update privileges on this table, but they would like to have these users to have insert & update privileges on this table, but one field would have to be set to a certain value when they did create a new record
So for a quick test I created a test copy of the table, created a role with insert/update privs on it, granted the role, and created a simple trigger:
create or replace trigger ops_test_insert
before insert on existing_table_test
for each row
enable
declare
begin
:new.specified_column := 'Mandatory';
end;
Did a test insert on the table and it works, but basically the trigger is doing just a default value for the column on any new record.
The column in question already has a default value, that is different from the one I set in the trigger.
How would I have the trigger have the :new.specified_column value = "Mandatory" only for users with the role I created, for all other users the column would be the devault value.
Thanks in advance.