CODEWITHSUNDEEP
sqldatabasesqlitetriggers
SeekingAlpha
SeekingAlpha

Reputation: 7797

update multiple tables with trigger

I want to update two tables when a user wants to update a view.

create trigger update_mID
instead of update of mID on LateRating
for each row
begin
    update Movie, Rating
    set mID = new.mID
    where mID = Old.mID;
end;

I want to update bot the Movie relation and the Rating relation, however, I have not yet experienced a trigger that is able to update multiple tables. Can someone please indicate how I can overcome this?

UPDATE: This is for a exercise to test my trigger scripting skills. The requirement is that I have to write it in one trigger query. @CL. I tried putting two update statements between the begin and end keywords, however, it says that there is a syntax error.... is there a specific way to put two updates between the begin and end?

Upvotes: 1

Views: 4510

Answers (2)

user10512966
user10512966

Reputation:

You could do a REPLACE INTO statement like the following:

DROP TRIGGER IF EXISTS `update_mID`;CREATE DEFINER=`USER`@`localhost` TRIGGER 
`update_mID` AFTER UPDATE ON `tblname` FOR EACH ROW REPLACE INTO 
USER_DATABASENAME.TBLNAME (COLUMNNAME1,COLUMNNAME1) SELECT COLUMNNAME1,COLUMNNAME1 
FROM USER_DBNAME.TBLNAME

This can even be two separate databases like the example below:

DROP TRIGGER IF EXISTS `update_mID`;CREATE DEFINER=`USER`@`localhost` TRIGGER 
`update_mID` AFTER UPDATE ON `tblname from DB1` FOR EACH ROW REPLACE INTO 
USER_DATABASENAME1.TBLNAMEDB2 (COLUMNNAME1,COLUMNNAME1) SELECT 
COLUMNNAME1,COLUMNNAME1 FROM USER_DBNAME2.TBLNAME

Upvotes: 1

CL.
CL.

Reputation: 180040

A single UPDATE statement can modify only a single table.

Use two UPDATEs:

UPDATE Movie  SET mID = NEW.mID WHERE mID = OLD.mID;
UPDATE Rating SET mID = NEW.mID WHERE mID = OLD.mID;

Upvotes: 2

Related Questions