当前位置：文江博客话题详情

需要写一个触发器

发布于 2024-11-07 12:16:04 字数 120 浏览 1 评论 0原文

我想为表“TRANSACTION”编写一个触发器。当插入新行时，我想触发将字段“TRANSACTIONID”更新为所有先前记录的最大值+1。

我对 SQL 不太了解。有人可以帮助我吗？

非常感谢

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

聽兲甴掵 2024-11-14 12:16:04

对于多用户环境来说，这确实是一个坏主意，因为它会将插入序列化到表中。通常的方法是使用 Oracle 序列：

create sequence transaction_seq;

create trigger transaction_bir before insert on transaction
for each row
begin
    :new.id := transaction_seq.nextval;
end;

要编写一个基于触发器的解决方案，该解决方案实际上获得最大当前值加 1，您需要编写一个复杂的 3 触发器解决方案以避免“变异表”问题。或者，您可以使用另一个表来创建一个更简单的解决方案来保存当前的最大值，如下所示：

create table transaction_max (current_max_id number);

insert into transaction_max values (0);

create trigger transaction_bir before insert on transaction
for each row
declare
    l_current_max_id number;
begin
    update transaction_max set current_max_id = current_max_id + 1
        returning current_max_id into l_current_max_id;
    :new.id := l_current_max_id;
end;

这将避免变异表问题并将序列化（减慢）插入，因此我认为这与使用序列相比没有任何优势。

This is a really bad idea for a multi-user environment, as it will serialise inserts into the table. The usual approach is to use an Oracle sequence:

create sequence transaction_seq;

create trigger transaction_bir before insert on transaction
for each row
begin
    :new.id := transaction_seq.nextval;
end;

To write a trigger based solution that actually got the max current value plus 1, you would need to write a complex 3-trigger solution to avoid the "mutating table" issue. Or you could create a simpler solution using another table to hold the current maximum value like this:

create table transaction_max (current_max_id number);

insert into transaction_max values (0);

create trigger transaction_bir before insert on transaction
for each row
declare
    l_current_max_id number;
begin
    update transaction_max set current_max_id = current_max_id + 1
        returning current_max_id into l_current_max_id;
    :new.id := l_current_max_id;
end;

This will avoid the mutating table issue and will serialize (slow down) inserts, so I don't see any advantage of this over using a sequence.

回复收藏 0 原文

隐诗 2024-11-14 12:16:04

CREATE TRIGGER trigger1 on TransactionTable
INSTEAD OF INSERT
AS
BEGIN

  DECLARE @MaxTranId INT

    SELECT
        @MaxTranId = MAX(TransactionId)
    FROM
        TransactionTable

  INSERT INTO TransactionTable
       SELECT 
            @MaxTranId + 1 ,
            RestOfYourInsertedColumnsHere , 
       FROM 
        inserted
END
GO

CREATE TRIGGER trigger1 on TransactionTable
INSTEAD OF INSERT
AS
BEGIN

  DECLARE @MaxTranId INT

    SELECT
        @MaxTranId = MAX(TransactionId)
    FROM
        TransactionTable

  INSERT INTO TransactionTable
       SELECT 
            @MaxTranId + 1 ,
            RestOfYourInsertedColumnsHere , 
       FROM 
        inserted
END
GO

回复收藏 0 原文

~没有更多了~