PostgreSQL有条件的禁止修改和删除数据

1 公共触发器函数

• 触发器函数在CREATE TRIGGER时可以设置参数，因此cast(TG_ARGV[0] as bigint) 类型转换也只会在开发阶段发生异常
• 传递给触发器函数的参数通过特殊变量TG_ARGV获取
• TG_ARGV数据类型是text数组，因此注意转换类型；它来自CREATE TRIGGER语句的参数。索引从 0 开始记数。非法索引（小于 0 或者大于等于tg_nargs）会导致返回一个空值。
• 创建触发器的表必须包含名称为objectid的字段，且字段类型为数字类型

drop function  if exists  tirggerSharedFunctionChange();
drop function  if exists  tirggerSharedFunctionTruncate();

/****************************************************************************************
	禁止修改数据触发器函数,包含2个参数
		$1[bigint]:
				当参数为null时直接抛出不允许修改异常
				当参数为数字时表字段objectid小于这个参数值将抛出不允许修改异常
		$2[text]:抛出的异常信息
	创建触发器的表必须包含名称为objectid的字段，且字段类型为数字类型

	TG_ARGV：数据类型是text数组，因此注意转换类型；来自CREATE TRIGGER语句的参数。索引从 0 开始记数。非法索引（小于 0 或者大于等于tg_nargs）会导致返回一个空值。
****************************************************************************************/
create or replace function tirggerSharedFunctionChange() 
	returns trigger 
as $$
		declare
        begin
			if ( 'UPDATE'=TG_OP )  then
				if('null'=TG_ARGV[0]) then
					raise exception 'update %',TG_ARGV[1];
				else					
					if( NEW.objectid < cast(TG_ARGV[0] as bigint) ) then --如果类型转换失败则抛出异常（只会在开发过程中出现）
						raise exception 'update %',TG_ARGV[1];
					else
						return NEW;
					end if;
				end if;
			elsif ('DELETE'=TG_OP ) then
				if('null'=TG_ARGV[0]) then
					raise exception 'delete %',TG_ARGV[1];
				else					
					if( OLD.objectid < cast(TG_ARGV[0] as bigint) ) then  --如果类型转换失败则抛出异常（只会在开发过程中出现）
						raise exception 'delete %',TG_ARGV[1];
					else
						return OLD;
					end if;
				end if;
			end if;
        end; 
$$ language plpgsql security definer;

/****************************************************************************************
	禁止Truncate触发器函数,包含1个参数
		$1[text]:抛出的异常信息
****************************************************************************************/
create or replace function tirggerSharedFunctionTruncate() 
	returns trigger 
as $$
		declare
        begin
			if ( 'TRUNCATE'=TG_OP ) then
				raise exception '%',TG_ARGV[0];
			end if;
        end; 
$$ language plpgsql security definer;

2 使用方法

“不允许修改或删除任何数据”和“有条件修改或删除数据”的区别只在于触发器函数tirggerSharedFunctionChange的第一个参数，设置为null表示禁止修改或删除，设置为整数表示objectid小于此数值的记录禁止修改或删除。

2.1 不允许修改或删除任何数据

drop trigger if exists tirggerAppsChange on apps;
drop trigger if exists tirggerAppsTruncate on apps;


create trigger tirggerAppsChange before update or delete on public.apps 
        for each row execute procedure tirggerSharedFunctionChange(null,'不允许修改应用信息数据');


create trigger tirggerAppsTruncate before truncate on public.apps 
        for each statement execute procedure tirggerSharedFunctionTruncate('不允许截断应用信息数据');
/**
测试全部失败
update apps set name='aaa';
delete from apps;
truncate apps;
*/

2.2 有条件修改或删除数据

例如字典表objectid <10000的记录表示系统记录，不允许修改或删除。objectid >=10000可以随意修改或删除。

/****************************************************************************************
	字典表
drop table if exists  dictionarys;
****************************************************************************************/
create table dictionarys(
	objectid serial not null,															--唯一编号
	name text not null,																	--字典名称
	describe text,																			--备注
	constraint pk_dictionarys_objectid primary key (objectid)  with (fillfactor=80) using index tablespace idxshareds
);

drop trigger if exists triTrunDictionarys on dictionarys;
drop trigger if exists tirfChaDictionarys on dictionarys;



create trigger tirggerDictionarysChange before update or delete on public.dictionarys 
    for each row execute procedure tirggerSharedFunctionChange(10000,'不允许修改应用信息数据');


create trigger tirggerDictionarysTruncate before truncate on public.dictionarys 
    for each statement execute procedure tirggerSharedFunctionTruncate();


/**
update dictionarys set name='aaa';  --fail
update dictionarys set name='aaa' where objectid=10000; --ok
delete from dictionarys;  --fail
delete from dictionarys where objectid=10000; --ok;  --ok
truncate dictionarys;
*/