"Instead of" Triggers in mysql

Question

I have a customer.

CREATE TABLE `customer` (
    `cNic` VARCHAR(50) NOT NULL DEFAULT '',
    `cName` VARCHAR(100) NULL DEFAULT NULL,
    `cAddress` VARCHAR(500) NULL DEFAULT NULL,
    `cTp` VARCHAR(50) NULL DEFAULT NULL,
    `cEmail` VARCHAR(50) NULL DEFAULT NULL,
    `creditLimit` DOUBLE NULL DEFAULT NULL,
    `CustomerStatus` INT(11) NULL DEFAULT '0',
    PRIMARY KEY (`cNic`)
)

When a new row is added to this customer table, If the cNic (primary key) of the new row is already in the customer table I want to do an update to the existing (old) row with the the other fields. (Update customer set cName='NewName' where cNic = 'enteredNic' ).

Otherwise (If the cNic is not in the table) insert a new row. I know I can do this in mssql using instead of triggers. Is there is a way to do this in mysql.

score 5 · Accepted Answer · edited May 23 '17 at 11:46

5

MySQL does not have instead of triggers, but it does have REPLACE and INSERT...ON DUPLICATE KEY UPDATE (see documentation).

Also see my answer to "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

edited May 23 '17 at 11:46

Community

1
1

answered Aug 24 '14 at 08:06

Bill Karwin

538,548
86
673
828

score 1 · Answer 2 · edited Jun 28 '17 at 18:48

Another thing that worth notice is views in MySQL are updatable, meaning that your insert or update statement on views will automatically operate on your base table. Of course under the condition that your insert or update action won't cause any ambiguity.

For example you have a table R(A, B) which has one tuple (1, 2). And the view V(A) is (1). If you add (10) to V, it will cause ambiguity since MySQL doesn't know it should insert (10, 1) or (10, 2) or (10, NULL) in the base table.

More information here

"Instead of" Triggers in mysql

2 Answers2

Linked