Re: Check user roles in trigger?

Dr Drudge wrote:

> OK, so what would the correct privs be?
> How can I assign rights to update-only-if-null (see above)?
> thanks

CREATE OR REPLACE TRIGGER only_if_null
BEFORE UPDATE
ON some_table
FOR EACH ROW DECLARE x VARCHAR2(100);

BEGIN
    dbms_application_info.read_client_info(x)     IF x = some_value AND :OLD.some_column IS NULL THEN       RAISE_APPLICATION_ERROR(-20001, 'What did you do?');     END IF;
END only_if_null;
/

For demos using dbms_application_info:
http://www.psoug.org/reference/dbms_applic_info.html

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)