CODEWITHSUNDEEP
oracle-databasedatabase-trigger
Guillaume Levesque
Guillaume Levesque

Reputation: 45

Increment new column after insert

I have a question for all of you. I'm quite new in SQL and searched for more than 2 hours and didn't find exactly what I need.

I'm having a table in SQL named Courses. Here is the constructor:

CREATE TABLE Courses
(sign     VARCHAR2(6)   NOT NULL,
title     VARCHAR(50)   NOT NULL,
credits   INTEGER       NOT NULL,
CONSTRAINT  PrimaryKeyCourses  PRIMARY KEY (sign)
);

I have to add a new column, which I did with :

ALTER TABLE Courses ADD frequency INTEGER;

I want to create a trigger which will increment every time a new courses is added.

I tried to do this :

CREATE TRIGGER fq
AFTER INSERT ON Courses
FOR EACH ROW
UPDATE frequency SET frequency = frequency + 1;

But it doesn't seems to work properly :( I don't know what to do.

Upvotes: 0

Views: 106

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65323

No need to use an UPDATE statement, use a SELECT statement with max(value)+1. And to be able to change a :new. value, need to convert trigger to BEFORE type.

So, you can use the one as below

CREATE OR REPLACE TRIGGER fq
BEFORE INSERT ON Courses
FOR EACH ROW
DECLARE
BEGIN
    select nvl(max(frequency),0)+1
      into :new.frequency
      from Courses;
END;

Of course you need a commit after a DML statement, I think it's better to include only one commit outside of this trigger after INSERT statement applied on Courses table, because of providing transaction integrity rule.

P.S. I know you're restricted to use a trigger, but Using a sequence for the value of column frequency is a better, practical alternative as @nikhil sugandh suggested. In this case a trigger is not needed. If you're using DB version 12c, you can add that sequence as default for the column frequency as frequency INTEGER GENERATED ALWAYS AS IDENTITY during the table creation.

Upvotes: 2

Nikhil
Nikhil

Reputation: 3950

use sequence :

  CREATE SEQUENCE Courses_frequency
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

and do insert like:

INSERT INTO Courses 
(sign,title,credits,frequency)
VALUES
(value1,value2,value3,Courses_frequency.NEXTVAL);

Upvotes: 0

Related Questions