插入后需要创建一个触发器来增加表中的值 [英] Need to create a trigger that increments a value in a table after insertion

问题描述

我当前正在使用的当前游戏数据库原型上的触发器存在问题

I'm currently having a problem with triggers on a current prototype of a game database i'm working on

所以，我有这两个表

CREATE TABLE public.hunters
(   id integer NOT NULL,
    name character varying(30) COLLATE pg_catalog."default" NOT NULL,
    weapon character varying(30) COLLATE pg_catalog."default" NOT NULL,
    ranking character varying(30) COLLATE pg_catalog."default" NOT NULL,
    nhunts integer NOT NULL,
    sex character(1) COLLATE pg_catalog."default" NOT NULL,
    title character varying(30) COLLATE pg_catalog."default",
)

CREATE TABLE public.hunts
(
    id_h integer NOT NULL,
    id_m integer NOT NULL,
    id_l integer NOT NULL,
    code integer NOT NULL,
    huntname character varying(50) COLLATE pg_catalog."default",
)

还有其他表，但是问题围绕着这两个表.

There are other tables, but the problem revolves around those two.

看，狩猎是一个表格，其中包含猎人的ID，要被狩猎的怪物以及将被狩猎的怪物的位置-以及que hunt quest的名称.每次猎人打猎一些怪物时，应该在桌子上的猎人身上增加"nhunts"值.

See, a hunt is a table that contains the id of a hunter, the monster to be hunted and the location where the monster will be hunted - and also the name of que hunting quest. Everytime a hunter hunts some monster, it should increment the "nhunts" value, there on the table hunters.

许多猎人可能会在许多不同的地方猎杀许多不同的怪物.代码列是一个序数值，代表一次狩猎的历史性(例如，如果代码等于20，则它是自"everever"以来的第20次狩猎-或至少是自首次注册以来的20次狩猎).

Many hunters may hunt many different monster, on many different locations. The code column is an ordinal value which represents how recent or old is a hunt (so, if code equals 20, it would be the 20th hunt since "ever" - or at least, since it first became registered)

问题是我不知道如何创建此触发器.我尝试了一切，但价值从未更新

Problem is I don't know how to create this trigger. I tried everything and the value never is updated

这是我尝试过的.但这只是不更新​​任何内容

This is what I tried. But it just doesn't update of increment anything

CREATE OR REPLACE FUNCTION Hunter_HuntsIncrement() RETURNS TRIGGER AS $$
DECLARE
    idv integer;
BEGIN
    idv := TG_ARGV[0];  
    IF (new.id_h = old.id_h AND new.code = old.code) THEN
        UPDATE hunters
            SET nhunts = nhunts + 1
        WHERE id = idv;
    END if;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER updating_nhunts AFTER INSERT ON hunts
FOR EACH STATEMENT EXECUTE PROCEDURE hunter_huntsincrement()

我知道这可能是非常错误的，但是我是触发器的新手，我真的很想在这里伸出援手

I know it's probably terribly wrong, but I'm new to triggers and I'd really like a helping hand here

推荐答案

保持摘要值是棘手的-很容易创建死锁您的程序.

Maintaining summary value is tricky - it's easy to create a possibility to deadlock your program.

如果确实要这样做，因为您知道否则会遇到性能问题(例如成百上千的nhunt)，那么最好为nhunt创建一个单独的摘要表，例如:

If you really have to do this, because you know that you'll have performance problems otherwise (like nhunts in hundreds or more), then it's better to create a separate summary table for nhunts, something like:

CREATE TABLE hunts_summary
(
    id_hs bigserial primary key,
    id_h integer NOT NULL,
    nhunts integer NOT NULL
);
CREATE INDEX hunts_summary_id_h_idx on hunts_summary(id_h);

狩猎触发器:

• 为每个添加，删除，更新的行运行；
• 在每次插入时添加一行(id_h，nhunts)=(NEW.id_h，1);
• 在每次删除操作时添加一行(id_h，nhunts)=(OLD.id_h，-1);
• 以上两种情况均会更改 id_h 的更新.

• runs for each added, removed, updated row;
• adds a row (id_h, nhunts) = (NEW.id_h, 1) on each insert;
• adds a row (id_h, nhunts) = (OLD.id_h, -1) on each delete;
• both of the above on update that changes id_h.

由于触发器将仅添加新行，因此不会锁定现有行，因此不会死锁.

As the trigger will only add new rows it does not lock existing rows and therefore it can't deadlock.

但这还不够-如上所述，摘要表将使行增长的速度比寻线表快或快，因此它并不是很有用.因此，我们需要添加某种方式来定期合并现有行-某些改变方式:

But this is not enough - as described above the summary table will grow rows as fast or faster than hunts table, so it's not very helpful. So we need to add some way to merge existing rows periodically - some way to change:

id_h nhunts
1    1
1    1
2    1
2    -1
1    1
1    -1
2    1
1    1
2    1

收件人:

id_h nhunts
1    3
2    2

此操作不应在每次触发调用时都运行，因为这样做会很慢，但可以随机运行-例如，随机每1/1024次调用.此功能将使用跳过锁定"关键字来避免触摸已锁定的行，否则会导致死锁.

This should not run on each trigger invocation, as it will then be quite slow, but it can run randomly - for example every 1/1024th invocation at random. This function will use "skip locked" keyword to avoid touching already locked rows, avoiding otherwise possible deadlock.

这种触发器看起来像这样:

Such trigger would look something like this:

create or replace function hunts_maintain() returns trigger
as $hunts_maintain$
        begin
                if (tg_op = 'INSERT') then
                        insert into hunts_summary(id_h, nhunts)
                                values (NEW.id_h, 1);
                elsif (tg_op = 'DELETE') then
                        insert into hunts_summary(id_h, nhunts)
                                values (OLD.id_h, -1);
                elsif (tg_op = 'UPDATE' and NEW.id_h!=OLD.id_h) then
                        insert into hunts_summary(id_h, nhunts)
                                values (OLD.id_h, -1), (NEW.id_h, 1);
                end if;

                if (random()*1024 < 1) then
                        with deleted_ids as (
                                select id_hs from hunts_summary for update skip locked
                        ),
                        deleted_nhunts as (
                                delete from hunts_summary where id_hs in (select id_hs from deleted_ids) returning id_h, nhunts
                        )
                        insert into hunts_summary (id_h, nhunts) select id_h, sum(nhunts) from deleted_nhunts group by id_h;
                end if;

                return NEW;
        end;
$hunts_maintain$ language plpgsql;

create trigger hunts_maintain
        after insert or update or delete on hunts
        for each row execute procedure hunts_maintain();

触发器在笔记本电脑上运行得足够快，可以在45秒内插入1M行以搜寻表.

The trigger runs fast enough on my laptop to insert 1M rows to hunts table in 45s.

下面的视图将使从摘要中提取当前nhunt变得容易.即使搜寻表将达到数十亿，查询它也会花费很少的时间或毫秒:

This view below will make it easy to extract current nhunts from summary. Querying it will take a small number or ms even if hunts table will be in billions:

create or replace view hunts_summary_view as
        select id_h, sum(nhunts) as nhunts
        from hunts_summary
        group by id_h;

这篇关于插入后需要创建一个触发器来增加表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！