先创建个触发器函数
CREATE OR REPLACE FUNCTION trfor100w()
RETURNS trigger AS
$BODY$
DECLARE
rowcounts integer;
BEGIN
IF TG_OP='INSERT' THEN
select count(*) into rowcounts from tablename;
--tablename替换为你需要测试是否有100W条数据的表名
IF rowcounts >= 1000000 THEN
--这里执行一个删除最老一条的数据的语句
--我不知道你数据表的结构,这个不好写,
--你自己写下,直接写入sql语句就可,别忘了
--最后要加分号,比如
-- delete * from xxx;
END IF;
return NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
之后在正式创建触发器
CREATE TRIGGER tg_trfor100w
BEFORE INSERT
ON tablename
FOR EACH ROW
EXECUTE PROCEDURE trfor100w();
这里的tablename也需要替换为你需要测试是否有100W条数据的表名
1 建立一个返回为trigger的过程
CREATE OR REPLACE FUNCTION after_alphas_id() RETURNS trigger AS $BODY$
BEGIN
IF( TG_OP='DELETE' ) THEN
UPDATE titles SET alpha_at=null WHERE id=OLD.title_id;
ELSE
UPDATE titles SET alpha_at=NOW() WHERE id=NEW.title_id;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql';
2 创建触发器
CREATE TRIGGER after_alphas_id
AFTER INSERT OR DELETE
ON alphas
FOR EACH ROW
EXECUTE PROCEDURE after_alphas_id();
只是一个例子,你拿去改改?
create trigger dd on a
for insert, delete
as
begin
declare @id int ,
@name varchar(500)
if update(id)
begin
select @id = id ,
@name = name
from inserted
if not exists ( select id
from dbo.b
where id = @id
and name = @name )
begin
insert dbo.b
( id, name )
values ( @id, @name )
end
end
else
begin
select @id = id ,
@name = name
from deleted
if exists ( select id
from dbo.b
where id = @id
and name = @name )
begin
delete from dbo.b
where id = @id
and name = @name
end
end
end
希望这个能够帮助到你
用户登录
还没有账号?立即注册
用户注册
投稿取消
文章分类: |
|
还能输入300字
上传中....