CODEWITHSUNDEEP
sqloracle-databasetriggers
Kevin
Kevin

Reputation: 6292

need to write a trigger

I want to write a trigger for a table "TRANSACTION".When a new line is inserted, I want to trigger to update the field "TRANSACTIONID" to the maximum + 1 of all the previous records.

I on't know much about SQL. Can someone help me?

many thanks

Upvotes: 0

Views: 101

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132580

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.

Upvotes: 6

bleeeah
bleeeah

Reputation: 3604

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

Upvotes: 0

Related Questions