Task - Triggers - org DB #12

akash-coded · 2022-03-17T03:26:44Z

akash-coded
Mar 17, 2022
Maintainer

Database = `org`

Dump file: org.sql

DB Schema

INSERT Triggers

Write a query to insert a new record into the worker table with worker_id set to 10. Use any values for the remaining columns. Define a trigger that automatically inserts a new record into the title table with the worker_title column set to "New Joinee", when a new worker record is inserted into the worker table.

UPDATE Triggers

Write a query to update worker_id of worker 10 to 101 in the worker table. Define a trigger that automatically updates all matching records in the title table whose worker_ref_id column matches the worker_id of the worker record being updated in the worker table.

DELETE Triggers

Write a query to delete the record of the worker with worker_id 101 in the worker table. Define a trigger that automatically deletes all matching records in the title table whose worker_ref_id column matches the worker_id of the worker record being deleted in the worker table.

Khyati-tripathi · 2022-03-17T06:43:36Z

Khyati-tripathi
Mar 17, 2022

--1
DELIMITER //
create Trigger after_worker_insert
after insert
on worker
for each row
begin
insert into title
values (NEW.worker_id,'just joined',current_timestamp);
END
DELIMITER;

--2
DELIMITER //
create Trigger after_worker_update
after update
on worker
for each row
begin
update title
set (worker_ref_id=NEW.worker_id) where(worker_ref_id=OLD.worker_id);
END
DELIMITER;

-3
DELIMITER //
create Trigger after_worker_delete
after delete
on worker
for each row
begin
delete from title
where(worker_ref_id=OLD.worker_id);
END
DELIMITER;

0 replies

Sarfinaa · 2022-03-17T06:47:37Z

Sarfinaa
Mar 17, 2022

1.) Trigger for insert
CREATE TRIGGER after_worker_insert
    AFTER INSERT
    ON worker
    FOR EACH ROW
BEGIN
        INSERT INTO title
            VALUES (
                NEW.w, "New Joinee", NOW()
            );
END //

INSERT INTO worker
    VALUES (
        10, 'Karan', 'Seth', 1000, NOW(), 'Account'
    );
2.)
CREATE TRIGGER after_worker_update
    AFTER UPDATE
    ON worker
    FOR EACH ROW
BEGIN
       UPDATE title SET worker_ref_id=NEW.worker_id WHERE worker_ref_id=OLD.worker_id;
END //
ALTER TABLE worker MODIFY worker_id INT;
UPDATE  worker
SET worker_id=101 WHERE worker_id=10;
3.)
CREATE TRIGGER after_worker_delete
    AFTER DELETE
    ON worker
    FOR EACH ROW
BEGIN
       DELETE FROM title WHERE worker_ref_id=OLD.worker_id;
END //

DELETE FROM worker WHERE worker_id=10;

0 replies

preeti-max · 2022-03-17T06:51:29Z

preeti-max
Mar 17, 2022

CREATE TRIGGER trigger1 AFTER INSERT ON worker FOR EACH ROW INSERT INTO
TITLE VALUES(NEW.WORKER_ID,"New Joinee",NOW());

CREATE TRIGGER trigger2 AFTER UPDATE ON worker FOR EACH ROW UPDATE title
SET WORKER_REF_ID=NEW.WORKER_ID WHERE WORKER_REF_ID=OLD.WORKER_ID;

CREATE TRIGGER trigger3 AFTER DELETE ON WORKER FOR EACH ROW DELETE FROM
TITLE WHERE WORKER_REF_ID=OLD.WORKER_ID;

0 replies

ratnadeepikavuddagiri · 2022-03-17T06:51:35Z

ratnadeepikavuddagiri
Mar 17, 2022

CREATE TRIGGER insert_worker
    AFTER INSERT
    ON worker
    FOR EACH ROW
BEGIN
        INSERT INTO title VALUES (NEW.worker_id, "New Joinee", NOW());
END //
delimiter ;
insert into worker values(10,"Deepika","Vuddagiri",10000,now(),"Admin");

CREATE TRIGGER update_worker 
    AFTER UPDATE 
    ON worker
    FOR EACH ROW 
alter table 
UPDATE title SET worker_ref_id = NEW.worker_id WHERE worker_ref_id = OLD.worker_id;
end//
delimiter ;
update worker set worker_id = 101 where worker_id =10;

delimiter //
CREATE TRIGGER delete_worker 
	AFTER DELETE 
	ON worker 	
	FOR EACH ROW
begin 
	delete from title where worker_ref_id = old.worker_id;
end//
delimiter ;
delete from worker where worker_id =101;

0 replies

Shubhi27 · 2022-03-17T06:52:07Z

Shubhi27
Mar 17, 2022

**1**
DELIMITER $$
CREATE TRIGGER after_worker_insert
        AFTER INSERT 
        ON WORKER 
        FOR EACH ROW
BEGIN 
        INSERT INTO title
         VALUES(
            NEW.worker_id, "New Joinee", NOW()
         );
END $$
DELIMITER ;

**2**
CREATE TRIGGER after_worker_update
AFTER UPDATE 
ON worker 
FOR EACH ROW UPDATE title
SET WORKER_REF_ID = NEW.WORKER_ID WHERE WORKER_REF_ID = OLD.WORKER_ID;


**3**
DELIMITER $$
CREATE TRIGGER after_worker_delete
AFTER DELETE
ON worker
FOR EACH ROW
BEGIN
DELETE FROM title
WHERE(worker_ref_id = OLD.worker_id);
END $$
DELIMITER;

0 replies

KaminiJha · 2022-03-17T06:57:19Z

KaminiJha
Mar 17, 2022

--1
CREATE TRIGGER after_worker_insert 
    AFTER INSERT ON worker
    FOR EACH ROW 
INSERT title
VALUES(NEW.worker_id,"New Joinee",NEW.JOINING_DATE);

INSERT WORKER 
VALUES(10,"KAMINI","JHA",700000,now(),"IT");
--2
CREATE TRIGGER after_worker_update 
    AFTER UPDATE ON worker
    FOR EACH ROW 
UPDATE title
SET 
    worker_ref_id = NEW.worker_id
WHERE
    worker_ref_id = OLD.worker_id;


UPDATE worker 
SET 
    worker_id = 101,
WHERE
    WORKER_ID = 10;

--3
CREATE TRIGGER after_worker_delete
    AFTER DELETE ON worker
    FOR EACH ROW 
DELETE FROM title
WHERE WORKER_REF_ID=OLD.WORKER_ID;
DELETE FROM worker 
where worker_id=101

0 replies

Harshita-Joshi · 2022-03-17T07:01:07Z

Harshita-Joshi
Mar 17, 2022

1

USE org;

DELIMITER //

--1
CREATE TRIGGER after_insert
AFTER INSERT
ON worker
FOR EACH ROW 
    BEGIN
        INSERT INTO title
        VALUES(NEW.worker_id, "New Joinee", NOW());
    END //

DELIMITER //

INSERT INTO worker VALUES(10, "Varun", "Vyas", 40000, NOW(), "HR");

2

USE org;

DELIMITER //

--1
CREATE TRIGGER after_update
AFTER UPDATE
ON worker
FOR EACH ROW 
    BEGIN
        UPDATE title
        SET WORKER_REF_ID = NEW.WORKER_ID
        WHERE
            WORKER_REF_ID = OLD.WORKER_ID;
    END //

DELIMITER //

SET foreign_key_checks = 0;

UPDATE worker
SET WORKER_ID = 101
WHERE WORKER_ID = 10;

SET foreign_key_checks = 1;

3

USE org;

DELIMITER //

--1
CREATE TRIGGER after_delete
AFTER DELETE
ON worker
FOR EACH ROW 
    BEGIN
        DELETE FROM title
        WHERE WORKER_REF_ID = OLD.WORKER_ID;
    END //

DELIMITER //

SET foreign_key_checks = 0;

DELETE FROM worker
WHERE WORKER_ID = 101;

SET foreign_key_checks = 1;

0 replies

Ashwesh09 · 2022-03-17T07:37:05Z

Ashwesh09
Mar 17, 2022

use org;
DELIMITER // 
CREATE TRIGGER after_insert_into_worker
AFTER INSERT
ON worker FOR EACH ROW
BEGIN
    INSERT INTO title
        values(worker_ref_id,"New Joinee",NOW())
    ;
END // 
DELIMITER ;


DELIMITER //
CREATE TRIGGER after_update_into_worker
AFTER UPDATE
ON worker FOR EACH ROW
BEGIN
    UPDATE title 
    set worker_ref_id = NEW.worker_id WHERE worker_ref_id = OLD.worker_id;
END //
DELIMITER ;


DELIMITER //
CREATE TRIGGER after_delete_into_worker
AFTER DELETE
ON worker
FOR EACH ROW
BEGIN
       DELETE FROM title WHERE worker_ref_id=OLD.worker_id;
END //
DELIMITER ;

0 replies

RohitN09 · 2022-03-17T08:58:00Z

RohitN09
Mar 17, 2022

DELIMITER //

CREATE TRIGGER after_worker_insert
    AFTER INSERT
    ON worker
    FOR EACH ROW
BEGIN
        INSERT INTO title VALUES (NEW.worker_id, "New Joinee", NOW());
END //

DELIMITER ;

INSERT INTO worker
    VALUES (10, 'Rohit', 'Nayal', 1000, NOW(), 'Account');

DELIMITER //

CREATE TRIGGER after_worker_update
    AFTER UPDATE
    ON worker
    FOR EACH ROW
BEGIN
    UPDATE title 
    SET worker_ref_id=NEW.worker_id
    where worker_ref_id=OLD.worker_id;
END //

DELIMITER ;

UPDATE worker
SET worker_id = 101 where worker_id = 10;

DELIMITER //

CREATE TRIGGER after_worker_delete
    AFTER DELETE
    ON worker
    FOR EACH ROW
BEGIN
    DELETE FROM title 
    WHERE worker_ref_id = OLD.worker_id;
END //

DELIMITER ;

delete from worker where worker_id = 10;

0 replies

Gaurav-Joshi-31 · 2022-03-17T09:03:40Z

Gaurav-Joshi-31
Mar 17, 2022

**1**
DELIMITER $$
CREATE TRIGGER after_worker_insert
        AFTER INSERT 
        ON WORKER 
        FOR EACH ROW
BEGIN 
        INSERT INTO title
         VALUES(
            NEW.worker_id, "New Joinee", NOW()
         );
END $$
DELIMITER ;

**2**
CREATE TRIGGER after_worker_update
AFTER UPDATE 
ON worker 
FOR EACH ROW UPDATE title
SET WORKER_REF_ID = NEW.WORKER_ID WHERE WORKER_REF_ID = OLD.WORKER_ID;


**3**
DELIMITER $$
CREATE TRIGGER after_worker_delete
AFTER DELETE
ON worker
FOR EACH ROW
BEGIN
DELETE FROM title
WHERE(worker_ref_id = OLD.worker_id);
END $$
DELIMITER;

0 replies

lalit-ky · 2022-03-20T09:33:54Z

lalit-ky
Mar 20, 2022

----------------------------- 1 -----------------------------------

DELIMITER //

CREATE TRIGGER after_worker_insert
    AFTER INSERT
    ON worker
    FOR EACH ROW
    BEGIN
        INSERT INTO title VALUES 
            (NEW.worker_id,"NEW JOINEE",CURRENT_TIMESTAMP);
    END //

DELIMITER ;

INSERT INTO worker VALUES 
    (9, 'Lalit', 'Kumar', 57646, CURRENT_TIMESTAMP, 'Admin');

----------------------------- 2 -----------------------------------

DELIMITER //

CREATE TRIGGER after_worker_update
    AFTER UPDATE
    ON worker
    FOR EACH ROW
    BEGIN
        UPDATE title SET worker_ref_id = NEW.worker_id 
            WHERE worker_ref_id = OLD.worker_id;
    END //

DELIMITER ;

UPDATE worker SET worker_id = 101 WHERE worker_id = 9;

----------------------------- 3 -----------------------------------

DELIMITER //

CREATE TRIGGER after_worker_delete
    AFTER DELETE
    ON worker
    FOR EACH ROW
    BEGIN
        DELETE FROM title WHERE worker_ref_id = OLD.worker_id;
    END //

DELIMITER ;

DELETE FROM worker WHERE worker_id = 101;

0 replies

PavanArvapally · 2022-03-20T17:22:58Z

PavanArvapally
Mar 20, 2022

1. CREATE Trigger after_insert
AFTER INSERT on worker
for each row
BEGIN
INSERT into title VALUES (NEW.WORKER_ID, 'New Joinee', NOW());
END//

2. CREATE TRIGGER after_update
AFTER UPDATE on worker
FOR each ROW
BEGIN
UPDATE title
SET Worker_REF_ID = NEW.WORKER_ID WHERE worker_ref_id = OLD.worker_id;
END//

3. CREATE Trigger after_delete
after delete
on worker
FOR each ROW
BEGIN 
DELETE FROM title WHERE worker_ref_id = OLD.worker_id;
END//

0 replies

ujjwal18bit1167 · 2022-03-21T03:41:16Z

ujjwal18bit1167
Mar 21, 2022

CREATE TRIGGER insert_worker
    AFTER INSERT
    ON worker
    FOR EACH ROW
BEGIN
        INSERT INTO title VALUES (NEW.worker_id, "New Joinee", NOW());
END //
delimiter ;
insert into worker values(20,"Ujjwal","Gupta",30000,now(),"admin");

CREATE TRIGGER update_worker 
    AFTER UPDATE 
    ON worker
    FOR EACH ROW 
alter table 
UPDATE title SET worker_ref_id = NEW.worker_id WHERE worker_ref_id = OLD.worker_id;
end//
delimiter ;
update worker set worker_id = 101 where worker_id =10;

delimiter //
CREATE TRIGGER delete_worker 
	AFTER DELETE 
	ON worker 	
	FOR EACH ROW
begin 
	delete from title where worker_ref_id = old.worker_id;
end//
delimiter ;
delete from worker where worker_id =101;

0 replies

vysh675 · 2022-03-21T03:53:15Z

vysh675
Mar 21, 2022

1.DELIMITER $$
CREATE TRIGGER after_worker_insert
AFTER INSERT
ON WORKER
FOR EACH ROW
BEGIN
INSERT INTO title
VALUES(
NEW.worker_id, "New Joinee", NOW()
);
END $$
DELIMITER ;

2.CREATE TRIGGER after_worker_update
AFTER UPDATE
ON worker
FOR EACH ROW UPDATE title
SET WORKER_REF_ID = NEW.WORKER_ID WHERE WORKER_REF_ID = OLD.WORKER_ID;

3.DELIMITER $$
CREATE TRIGGER after_worker_delete
AFTER DELETE
ON worker
FOR EACH ROW
BEGIN
DELETE FROM title
WHERE(worker_ref_id = OLD.worker_id);
END $$
DELIMITER;

0 replies

SrideviYamala · 2022-03-21T16:11:13Z

SrideviYamala
Mar 21, 2022

USE org;

# 1
DELIMITER //
CREATE TRIGGER after_worker_insert
    AFTER INSERT
    ON worker
    FOR EACH ROW
BEGIN
        INSERT INTO title
            VALUES (
                NEW.worker_id, "New Joinee", NOW()
            );
END //
DELIMITER ;
INSERT INTO worker
    VALUES (
        10, 'Aiswarya', 'Seepana', 30000, NOW(), 'Account'
    );

# 2
DELIMITER //
CREATE TRIGGER after_worker_update
    AFTER UPDATE
    ON worker
    FOR EACH ROW
BEGIN
    UPDATE TITLE SET worker_ref_id = NEW.worker_id where worker_ref_id =OLD.worker_id;
END //
DELIMITER ;
SET foreign_key_checks = 0;
UPDATE worker SET worker_id = 101 where worker_id =10;

# 3
DELIMITER //
CREATE TRIGGER after_worker_delete
    AFTER DELETE
    ON worker
    FOR EACH ROW
BEGIN
    DELETE FROM TITLE WHERE worker_ref_id = OLD.worker_id;
END //
DELIMITER ;
DELETE FROM worker where worker_id=101;

0 replies

mkaifahmed · 2022-04-17T06:01:26Z

mkaifahmed
Apr 17, 2022

use org;
DELIMITER // 
CREATE TRIGGER after_insert_into_worker
AFTER INSERT
ON worker FOR EACH ROW
BEGIN
    INSERT INTO title
        values(worker_ref_id,"New Joinee",NOW())
    ;
END // 
DELIMITER ;


DELIMITER //
CREATE TRIGGER after_update_into_worker
AFTER UPDATE
ON worker FOR EACH ROW
BEGIN
    UPDATE title 
    set worker_ref_id = NEW.worker_id WHERE worker_ref_id = OLD.worker_id;
END //
DELIMITER ;


DELIMITER //
CREATE TRIGGER after_delete_into_worker
AFTER DELETE
ON worker
FOR EACH ROW
BEGIN
       DELETE FROM title WHERE worker_ref_id=OLD.worker_id;
END //
DELIMITER ;

0 replies

DivikaAgrawal · 2022-04-18T03:57:35Z

DivikaAgrawal
Apr 18, 2022

1..

DELIMITER //

CREATE TRIGGER after_worker_insert
AFTER INSERT
ON worker
FOR EACH ROW
BEGIN
INSERT INTO title VALUES
(NEW.worker_id,"NEW JOINEE",CURRENT_TIMESTAMP);
END //

DELIMITER ;

2..

DELIMITER //

CREATE TRIGGER after_worker_update
AFTER UPDATE
ON worker
FOR EACH ROW
BEGIN
UPDATE title SET worker_ref_id = NEW.worker_id
WHERE worker_ref_id = OLD.worker_id;
END //

DELIMITER ;

3..

DELIMITER //

CREATE TRIGGER after_worker_delete
AFTER DELETE
ON worker
FOR EACH ROW
BEGIN
DELETE FROM title WHERE worker_ref_id = OLD.worker_id;
END //

DELIMITER ;

0 replies

Gayathiriramalingam2024 · 2024-05-16T15:35:04Z

Gayathiriramalingam2024
May 16, 2024

1..
DELIMITER [[
CREATE TRIGGER after_worker_insert
AFTER_INSERT on WORKER
FOR EACH ROW
INSERT INTO title VALUES(NEW.Worker_id,"New Joinee",now());
END [[
DELIMITER;

DELIMITER [[
CREATE TRIGGER after_worker_update
AFTER_UPDATE on WORKER
FOR EACH ROW
UPDATE title SET Worker_id=NEW.Worker_id where Worker_id=OLD.worker_id
END [[
DELIMITER;

DELIMITER [[
CREATE TRIGGER after_worker_delete
AFTER_DELETE on WORKER
FOR EACH ROW
DELETE FROM title where Worker_id=OLD.worker_id
END [[
DELIMITER;

0 replies

Task - Triggers - org DB #12

Uh oh!

Uh oh!

akash-coded Mar 17, 2022 Maintainer

Database = org

Dump file: org.sql

DB Schema

INSERT Triggers

UPDATE Triggers

DELETE Triggers

Replies: 18 comments

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

1

2

3

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

akash-coded
Mar 17, 2022
Maintainer

Database = `org`