How to use Trigger to AUTO_INCREMENT id in Pl/SQL In SQL
PL/SQL triggers are block structures and predefined programs invoked automatically when some event occurs. They are stored in the database and invoked repeatedly in a particular scenario.To auto increment the ID, we will use BEFORE conditional trigger so that it is activated as the user try to insert the row into the table.
Syntax:
CREATE SEQUENCE sequence_name
START WITH 1
INCREMENT BY 1;
CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
SELECT sequence_name.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/
- Before Conditional trigger is defined to activate as the new rows are inserted in the table.
- sequence_name.NEXTVAL INTO :NEW.ID is used to auto-increment the id.
- FROM DUAL is a automatically created table used to execute function or operation which do not depend on any table data.
Example:
SET SERVEROUTPUT ON;
CREATE SEQUENCE play_id START WITH 1 INCREMENT BY 1;
CREATE TABLE PLAYER1(ID NUMBER PRIMARY KEY , NAME VARCHAR(30), SCORE NUMBER );
CREATE OR REPLACE TRIGGER id_trigger
BEFORE INSERT ON PLAYER1
FOR EACH ROW
BEGIN
SELECT play_id.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/
INSERT INTO PLAYER1 (NAME, SCORE) VALUES ('Ellyse Perry', 347);
INSERT INTO PLAYER1 ( NAME, SCORE) VALUES ('Smriti Mandhana', 300);
INSERT INTO PLAYER1 (NAME, SCORE) VALUES ('Shafali Verma', 309);
INSERT INTO PLAYER1 (NAME, SCORE) VALUES ('Deepti Sharma', 295);
INSERT INTO PLAYER1 (NAME, SCORE) VALUES ('Harmanpreet Kaur', 268);
SELECT * FROM PLAYER1;
Output:
Explanation:
- SET SERVEROUTPUT ON; is used to display the output generated from the code.
- play_id is the sequence created with a start value of 1 and with an increment value of 1.
- Player1 table is created with ID,NAME nad Score field.
- Trigger is defined with before insert effect to auto increment the id .
- As the rows are inserted ,id_trigger is triggered and the id field is auto incremented.
How to Create id With AUTO_INCREMENT in PL/SQL?
PL/SQL, short for Procedural Language/Structured Query Language, combines SQL capabilities with procedural programming. It supports variable declaration, control flow structures, functions, records, cursors, procedures, and triggers.
PL/SQL features a block structure with optional sections for variable declarations and exception handling. Its execution section handles SQL queries seamlessly. Overall, PL/SQL offers a robust environment for developing database-centric applications with enhanced control and error-handling capabilities.