- #Mysql management studio trigger update#
- #Mysql management studio trigger full#
#Mysql management studio trigger update#
The AFTER UPDATE trigger is almost identical: INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, we set the delimiter back to a semi-colon: It determines whether the deleted flag is set, sets the variable accordingly, and inserts a new record into the audit table: Our AFTER INSERT trigger can now be defined.
#Mysql management studio trigger full#
To create the full trigger code we must change delimiter to something else - such as $$. Our trigger body requires a number of SQL commands separated by a semi-colon ( ). The first MySQL command we’ll issue is a little unusual: It’s not necessary to define a DELETE trigger since a post is marked as deleted by setting it’s deleted field to true. We require two triggers - AFTER INSERT and AFTER UPDATE on the blog table. TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE The value for NEW.col_name can be changed in BEFORE INSERT and UPDATE triggers. Note that you can refer to columns in the subject table using OLD.col_name (the previous value) or NEW.col_name (the new value).
The trigger body a set of SQL commands to run. An AFTER trigger must be used if you want to reference the new/changed record as a foreign key for a record in another table. A BEFORE trigger must be used if you need to modify incoming data. This can either be BEFORE or AFTER an INSERT, UPDATE or DELETE. A single trigger can only monitor a single table. I prefer to use a name which describes the table and action, e.g. Note that the changetime field will automatically be set to the current time. When a record is UPDATEd in the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘EDIT’ or ‘DELETE’ if the deleted flag is set. When a record is INSERTed into the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘NEW’ (or ‘DELETE’ if it was deleted immediately). ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 `changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,ĬONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE `changetype` enum('NEW','EDIT','DELETE') NOT NULL, `blog_id` mediumint(8) unsigned NOT NULL, Therefore, when we physically DELETE a blog entry, it’s full audit history is also removed. All columns are indexed and a foreign key is defined for audit.blog_id which references blog.id. The following SQL creates the `audit` table. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Blog posts' `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0', `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, The following SQL creates the `blog` and indexes the deleted column: `audit`: stores a basic set of historical changes with a record ID, the blog post ID, the change type (NEW, EDIT or DELETE) and the date/time of that change. `blog`: stores a unique post ID, the title, content, and a deleted flag. 
We’ll create a small example database for a blogging application.
0 kommentar(er)
