Change 'username' with your mysql username and 'db_name' with your db name.
Since I would like to do the same things if the row is inserted, updated, or deleted, I would like to know what's happening in the trigger.
Edit CREATE TABLE `ACCOUNTS` ( `pk` bigint(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(9) unsigned NOT NULL, `edit_on` bigint(10) unsigned DEFAULT NULL, `status` varchar(1) NOT NULL DEFAULT 'A', PRIMARY KEY (`pk`) USING BTREE) ENGINE=Inno DB AUTO_INCREMENT=2147483726 DEFAULT CHARSET=latin1 DELIMITER $$ DROP TRIGGER IF EXISTS `codigo_video`$$ CREATE TRIGGER `codigo_video` BEFORE INSERT ON `videos` FOR EACH ROW BEGIN DECLARE ultimo_id, proximo_id INT(11); SELECT id INTO ultimo_id FROM videos ORDER BY id DESC LIMIT 1; SET proximo_id = ultimo_id+1; SET NEW.cassette = CONCAT(NEW.cassette, LPAD(proximo_id, 5, '0')); END$$ DELIMITER ; I have to say, this is a very crude way to solve this issue because it banks on the premise that no entry was added/deleted (thus increasing the autoincrement value from what the MAX(id) + 1 would yield).
Regardless it's the only solution I've found to accomplish what I need to and I feel like I can bank on that premise.
I'm attempting (due to moving from PHP to Django) to rename all my primary keys from TABLENAME_id to id without breaking my application. This is how i update a row in the same table on insert activation Code and email are rows in the table USER.
On insert i don't specify a value for activation Code, it will be created on the fly by mysql.
property must be defined for each trigger, all other properties are optional.
Only the passed properties will be updated, all others will remain unchanged.
Additionally to the standard trigger properties the method accepts the following parameters.
DELIMITER $$ DROP TRIGGER IF EXISTS `set Edit Status`$$ CREATE TRIGGER `set Edit Status` AFTER INSERT on ACCOUNTS FOR EACH ROW BEGIN update ACCOUNTS set status='E' where = NEW.edit_on ; END$$ DELIMITER ; Please suggest a workaround PS: I have already gone through Updating table in trigger after update on the same table, Insert into same table trigger mysql, Update with after insert trigger on same table and mysql trigger with insert and update after insert on table but they dont seem to answer my question.
PUT_LINE('Inserting'); WHEN UPDATING('salary') THEN DBMS_OUTPUT. PUT_LINE('Updating salary'); WHEN UPDATING('department_id') THEN DBMS_OUTPUT. PUT_LINE('Updating department ID'); WHEN DELETING THEN DBMS_OUTPUT.
create table Employee( 2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 / Table created.
SQL select * from Employee 2 / ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- -------------------- -------------------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected.