Oracle触发器3-DDL触发器(二)
ble VARCHAR2)
IS
SELECT column_name
FROM all_tab_columns
WHERE owner = cp_owner AND table_name = cp_table;
BEGIN
-- if it was a table that was altered...
IF ora_dict_obj_type = 'TABLE'
THEN
-- for every column in the table...
FOR v_column_rec IN curs_get_columns (
ora_dict_obj_owner,
ora_dict_obj_name
)
LOOP
-- if the current column was the one that was altered then say so
IF ora_is_alter_column (v_column_rec.column_name)
THEN
-- if the table/column is core
IF is_application_column (
ora_dict_obj_owner,
ora_dict_obj_name,
v_column_rec.column_name
)
THEN
RAISE_APPLICATION_ERROR (
-20001,
'Cannot alter core application attributes'
);
END IF; -- table/column is core
END IF; -- current column was altered
END LOOP; -- every column in the table
END IF; -- table was altered
END;
--属性函数返回值列表
CREATE OR REPLACE TRIGGER hr.what_privs
AFTER GRANT ON SCHEMA
DECLARE
v_grant_type VARCHAR2 (30);
v_num_grantees BINARY_INTEGER;
v_grantee_list ora_name_list_t;
v_num_privs BINARY_INTEGER;
v_priv_list ora_name_list_t;
BEGIN
v_grant_type := ora_dict_obj_type;
v_num_grantees := ora_grantee (v_grantee_list);
v_num_privs := ora_privilege_list (v_priv_list);
IF v_grant_type = 'ROLE PRIVILEGE'
DBMS_OUTPUT.put_line (
CHR (9) || 'The following roles/privileges were granted'
);
FOR counter IN 1 .. v_num_privs
LOOP
DBMS_OUTPUT.put_line (
CHR (9) || CHR (9) || 'Privilege ' || v_priv_list (counter)
);
END LOOP;
ELSIF v_grant_type = 'OBJECT PRIVILEGE'
THEN
DBMS_OUTPUT.put_line (
CHR (9) || 'The following object privileges were granted'
);
FOR counter IN 1 .. v_num_privs
LOOP
DBMS_OUTPUT.put_line (
CHR (9) || CHR (9) || 'Privilege ' || v_priv_list (counter)
);
END LOOP;
DBMS_OUTPUT.put (CHR (9) || 'On ' || ora_dict_obj_name);
IF ora_with_grant_option
THEN
DBMS_OUTPUT.put_line (' with grant option');
ELSE
DBMS_OUTPUT.put_line ('');
END IF;
ELSIF v_grant_type = 'SYSTEM PRIVILEGE'
THEN
DBMS_OUTPUT.put_line (
CHR (9) || 'The following system privileges were granted'
);
FOR counter IN 1 .. v_num_privs
LOOP
DBMS_OUTPUT.put_line (
CHR (9) || CHR (9) || 'Privilege ' || v_priv_list (counter)
);
END LOOP;
ELSE
DBMS_OUTPUT.put_line ('I have no idea what was granted');
END IF;
FOR counter IN 1 .. v_num_grantees
LOOP
DBMS_OUTPUT.put_line (
CHR (9) || 'Grant Recipient ' || v_grantee_list (counter)
);
END LOOP;
END;
/