Scribd
Upload
11 views2 pages

DB Note Trigger

The document describes four database triggers related to tournament and organizer management. The first trigger populates the tournament_type in the info table based on tournament_id, the second logs changes to organizer details, the third validates tournament_id in the organizer table, and the fourth generates a new tournament_id before inserting into the Tournament table. Each trigger ensures data integrity and proper logging of changes in the database.

Uploaded by

Jamilur Reza
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
11 views2 pages

DB Note Trigger

The document describes four database triggers related to tournament and organizer management. The first trigger populates the tournament_type in the info table based on tournament_id, the second logs changes to organizer details, the third validates tournament_id in the organizer table, and the fourth generates a new tournament_id before inserting into the Tournament table. Each trigger ensures data integrity and proper logging of changes in the database.

Uploaded by

Jamilur Reza
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

----------------------------------------------------------------

1. This trigger sets the tournament_type column in the info table based on the
tournament_id provided in the new row.

CREATE OR REPLACE TRIGGER insert_info_trigger


BEFORE INSERT ON info
FOR EACH ROW
BEGIN
SELECT tournament_type INTO :new.tournament_type
FROM Tournament
WHERE tournament_id = :new.tournament_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL; -- Do nothing if no matching tournament is found
END;

INSERT INTO info (tournament_id, duration)


VALUES (3, 100);

INSERT INTO info (tournament_id, duration)


VALUES (2, 60);

----------------------------------------------------------------
2. This trigger records changes to the organizer table, capturing the old and new
values of organizer_name and tournament_id.

CREATE OR REPLACE TRIGGER organizer_update_trigger


BEFORE UPDATE ON Organizer
FOR EACH ROW
BEGIN
IF :OLD.organizer_name != :NEW.organizer_name OR :OLD.tournament_id !
= :NEW.tournament_id THEN
INSERT INTO organizer_change_record (organizer_id, old_organizer_name,
new_organizer_name, old_tournament_id, new_tournament_id, change_date)
VALUES
(:OLD.organizer_id, :OLD.organizer_name, :NEW.organizer_name, :OLD.tournament_id, :
NEW.tournament_id, SYSDATE);
END IF;
END;

---------------------------------------------------------------
3. This trigger checks whether the TOURNAMENT_ID provided in the ORGANIZER table
exists in the TOURNAMENT table before insertion or update.

CREATE OR REPLACE TRIGGER trg_check_organizer_tournament


BEFORE INSERT OR UPDATE ON ORGANIZER
FOR EACH ROW
DECLARE
v_tournament_count NUMBER;
BEGIN
IF :NEW.TOURNAMENT_ID IS NOT NULL THEN
SELECT COUNT(*) INTO v_tournament_count
FROM TOURNAMENT
WHERE TOURNAMENT_ID = :NEW.TOURNAMENT_ID;

IF v_tournament_count = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid tournament ID. this tournament
id is not exist on the Tournament table so Cannot insert or update organizer.');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -20001 THEN
RAISE;
END IF;
END;

---------------------------------------------------------------
4. This trigger generates a new TOURNAMENT_ID before insertion into the Tournament
table.

CREATE OR REPLACE TRIGGER tournament_trigger


BEFORE INSERT ON Tournament
FOR EACH ROW
BEGIN
SELECT tournament_seq.NEXTVAL INTO :new.tournament_id FROM dual;
END;

You might also like