Trigger in Database: How to Create Triggers in MySQL with example.
Trigger in Database: How to Create Triggers in MySQL with example.
Hello,Everyone I am back with my blog about Triggers: Includes definition of trigger, concepts of
triggers, real example of trigger database as follows:
A trigger:
• Is a PL/SQL block or a PL/SQL procedure associated with a
table, view, schema, or the Database
• Executes implicitly whenever a particular event takes
place
• Can be either:
– Application trigger: Fires whenever an event occurs with a
particular application
– Database trigger: Fires whenever a data event (suchAs DML)
or system event (such as logon or shutdown) occurs on a schema or database.
Type of Triggers:
Types of Trigger
|
|||
Row
|
Before
|
Insert
|
|
Update
|
|||
After
|
|||
Delete
|
|||
Statement
|
Before
|
Insert
|
|
Update
|
|||
After
|
|||
Delete
|
|||
To create required tables for triggers
CREATE TABLE Test1
AS
SELECT * FROM
Pradip_Test WHERE 1=2
CREATE TABLE Test2
AS
SELECT * FROM
Pradip_Test WHERE 1=2
Example of Simple of Simple Trigger
CREATE OR
REPLACE TRIGGER Tr_del
BEFORE DELETE
ON Pradip_test
FOR EACH
ROW
BEGIN
INSERT INTO
Test1
VALUES
(
:OLD.eno,
:OLD.ename,
:OLD.sal,
:OLD.jn_dt
);
END;
DELETE FROM
Pradip_test
SELECT * FROM
Pradip_test
SELECT * FROM
Test1
ROLLBACK
DROP TRIGGER
Tr_del
CREATE OR
REPLACE TRIGGER Tr_stm_lvl
BEFORE DELETE
ON Pradip_test
BEGIN
INSERT INTO
Test2
VALUES
(
3,
'Deleting',
NULL,
sysdate
);
END;
TRUNCATE TABLE
test2
TRUNCATE TABLE
Pradip_test
DELETE FROM
Pradip_test
SELECT * FROM
test1
SELECT * FROM
test2
SELECT * FROM
Pradip_test
Example of Simple Trigger before Delete
CREATE OR
REPLACE TRIGGER Tr_before_insert
BEFORE INSERT
ON Pradip_test
FOR EACH
ROW
DECLARE
v_username varchar2(10);
BEGIN
IF :NEW.JN_dt = sysdate THEN
:NEW.sal
:=99999;
END IF;
END;
INSERT INTO
Pradip_test
VALUES (1,'Test Trg',2222,sysdate)
SELECT * FROM
Pradip_test
INSERT INTO
Pradip_test
VALUES (1,'Test Trg 2',2222,sysdate+1)
SELECT * FROM
Pradip_test
DELETE FROM
Pradip_test
SELECT * FROM Pradip_test
SELECT * FROM Test1
Example of Row Level After Trigger
grant execute on dbms_lock to
scott
CREATE OR
REPLACE TRIGGER Tg_aft_test
AFTER INSERT
ON Pradip_test
FOR EACH
ROW
BEGIN
dbms_lock.sleep(10);
INSERT INTO
test1
VALUES (:NEW.eno,
:NEW.ename,
:NEW.sal,
SYSTIMESTAMP);
END;
INSERT INTO
Pradip_test
VALUES (1,'Pradip',5000,SYSTIMESTAMP+1)
Select *
from Pradip_test
Select *
from test1
Example of Statement Level Trigger
CREATE OR
REPLACE TRIGGER trg_Stm_dml
AFTER INSERT
OR UPDATE OF sal OR DELETE ON Pradip_test
BEGIN
IF INSERTING THEN
INSERT INTO test_trg
VALUES
(
1,
user,
'Inserting',
sysdate
);
ELSIF UPDATING THEN
INSERT INTO test_trg
VALUES
(
2,
user,
'Updating',
sysdate
);
ELSIF deleting THEN
INSERT INTO test_trg
VALUES
(
3,
user,
'Deleting',
sysdate
);
END IF;
END;
/
DELETE FROM
Pradip_test
SELECT * FROM Pradip_test
SELECT * FROM Test1
SELECT * FROM test_trg
Example of
Mutating error
CREATE OR
REPLACE TRIGGER Tr_del
AFTER INSERT
ON Pradip_test
FOR EACH
ROW
DECLARE
l_sql number;
BEGIN
SELECT 1 INTO l_sql FROM Pradip_test;
dbms_output.put_line (l_sql);
END;
Example of AUTONOMOUS_TRANSACTION;
CREATE OR
REPLACE TRIGGER trg_Stm_dml
AFTER INSERT
OR UPDATE OF sal OR DELETE ON Pradip_test
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF INSERTING THEN
INSERT INTO test_trg
VALUES
(
1,
user,
'Inserting',
sysdate
);
ELSIF UPDATING THEN
INSERT INTO test_trg
VALUES
(
2,
user,
'Updating',
sysdate
);
ELSIF deleting THEN
INSERT INTO test_trg
VALUES
(
3,
user,
'Deleting',
sysdate
);
END IF;
COMMIT;
END;
/
DELETE FROM Pradip_test
SELECT * FROM Pradip_test
Finally thank you for sparing your valuable time on reading my blog and stay connected for my next blog.Thank You.Have a good time.
Comments
Post a Comment