Trigger using old, new table, inserting into two tables

user_10387326 0
Viewed 75

I'm new at creating Triggers in SQL and trying to get this one working (using Mysql). It's supposed to take the new (update) version of a blog entry and put it into the table called EditedPosts, and just leave the original row in the table Posts unchanged.

I've tried a few different ways but cannot get it to work. Anybody have any ideas?

My trigger:

DELIMITER $$ 
CREATE TRIGGER editedPost 
BEFORE UPDATE on Posts
for each row
begin
insert into editedPosts VALUES (PostID, Titel, Content, Created, UserName, BlogID) FROM new;
insert into Posts VALUES (PostID, Titel, Content, Created, UserName, BlogID) FROM old;

END $$
DELIMITER ;

My table Posts:

create table Posts (PostID INTEGER AUTO_INCREMENT not null,
                          Titel VARCHAR (50) not null,
                          Content TEXT not null,
                          Created timestamp DEFAULT current_timestamp,
                          UserName VARCHAR (50) not null,
                          BlogID INTEGER not null,
                          primary key (PostID),
                          foreign key(UserName) references User_Accounts(UserName),
                          foreign key (BlogID) REFERENCES Blogs (BlogID));

My table EditedPosts:

create table EditedPosts (EPostID INTEGER AUTO_INCREMENT not null,
                          PostID INTEGER not null,
                          Titel VARCHAR (50) not null,
                          Content TEXT not null,
                          Created timestamp,
                          Edited timestamp DEFAULT current_timestamp,
                          UserName VARCHAR (50) not null,
                          BlogID INTEGER not null,
                          primary key (EPostID),
                          foreign key (PostID) REFERENCES Posts(PostID),
                          foreign key(UserName) references User_Accounts(UserName),
                          foreign key (BlogID) REFERENCES Blogs (BlogID));
triggers mysql sql
1 Answers

I would say this is more commonly done as an after update trigger. But that doesn't really make a difference.

In any case, you don't need to update the row in Posts at all. Then you need to fix your insert into EditedPosts:

DELIMITER $$ 
CREATE TRIGGER trg_post_update
BEFORE UPDATE on Posts
for each row
begin
    insert into editedPosts (PostID, Titld, Content, Created, UserName, BlogId)
        VALUES (new.PostID, new.Title, new.Content, new.Created, new.UserName, new.BlogID);
END $$
DELIMITER ;

Actually, if you want to keep the original values . . . rather than updating them.

I don't think MySQL has an instead of trigger. And I don't think there is a way to prevent the update without generating an error. That leaves reassigning the values:

DELIMITER $$ 
CREATE TRIGGER trg_post_update
BEFORE UPDATE on Posts
for each row
begin
    insert into editedPosts (PostID, Titld, Content, Created, UserName, BlogId)
        VALUES (new.PostID, new.Title, new.Content, new.Created, new.UserName, new.BlogID);

    set new.PostID = old.PostId,
        new.Title = old.Title,
        new.Content = old.Content,
        new.Created = old.Created,
        new.UserName = old.UserName,
        new.BlogID = old.BlogID;
END $$
DELIMITER ;

For this, you really do want a before update trigger.

user_11440350
Related