MySQL Triggers

[uniquejava]

Introduction to MySQL AFTER UPDATE triggers

不能像oracle那样

unfortunately we can't use in MySQL after INSERT or UPDATE description, like in Oracle

MySQL Fire Trigger for both Insert and Update

cyper 实战

# drop trigger ON_GRADE_UPDATE;
create trigger ON_GRADE_UPDATE
    after update
    on customer
    for each row

begin
    if old.grade <> new.grade THEN
        insert into event(event_title, event_description, start_date, end_date, start_time, end_time,
                          is_full_day_event,
                          is_recurring, created_by,
                          created_at, parent_event_id)
        values ('customer grade changed!', concat('from ', old.grade, ' to ', new.grade), now(), null,
                null, null, 'N', 'N', 'cyper', now(), null);
    end if;

end;

drop trigger if exists comment_count_changes;
create trigger comment_count_changes
    after insert
    on cms_comment
    for each row
begin
    select t.created_by, t.title into @author, @title from cms_post t where id = new.post_id;

    if @author = 2 then
        insert into event(event_title, event_description, start_date, end_date, start_time, end_time,
                          is_full_day_event,
                          is_recurring, created_by,
                          created_at, parent_event_id)
        values (@title, concat('刚有人回复了你的帖子:', new.body), now(), null,
                null, null, 'N', 'N', 'cyper', now(), null);
    end if;

end;