CODEWITHSUNDEEP
mysql
EastsideDev
EastsideDev

Reputation: 6639

Trigger to set slug before updating row

I run the following MySQL code to create the full_contact_social_networks table

 CREATE TABLE `full_contact_social_networks` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `base_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `slug` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `full_contact_social_networks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `base_id` (`base_id`);

ALTER TABLE `full_contact_social_networks`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

I then want to create a trigger, to set the value of the slug column, to the sha256 of base_id+name. I am using the following:

CREATE TRIGGER full_contact_social_networksUpd BEFORE UPDATE ON full_contact_social_networks 
FOR EACH ROW SET `slug` = SHA2((CONCAT (`base_id`, `name`)), 256);

But this is giving me the following error message:

[errno] => 1193
[sqlstate] => HY000
[error] => Unknown system variable 'slug'

Upvotes: 1

Views: 380

Answers (1)

e4c5
e4c5

Reputation: 53734

Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case sensitive.

ref: http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

So your trigger will have to be:

CREATE TRIGGER full_contact_social_networksUpd BEFORE UPDATE ON full_contact_social_networks 
FOR EACH ROW SET NEW.slug = SHA2((CONCAT (NEW.base_id, NEW.name)), 256);

But this only works for updates, you probably want one for inserts as well. In mysql you cannot have a trigger that says INSERT OR UPDATE you need two triggers

 CREATE TRIGGER full_contact_social_networksIns BEFORE INSERT ...

Upvotes: 1

Related Questions