MySQL Trigger Syntax – How to Write an Insert Trigger

insertMySQLsyntaxtrigger

I am very new to sql. feel a little stupid to ask this question

Say I have this table Warehouse(shipperid, name , state). Then I define another table warehouse_maine (shipperid, name, state) which should only contain the warehouse located in maine. after I insert all the data for warehouse. I want to create a trigger for warehouse_maine, which insert the warehouse data only in maine state.

CREATE TRIGGER TRIGGER01 ON WAREHOUSE_MAINE
FOR INSERT
AS select * from warehouse where warehouse.state= maine

This is what I thought but it's obviously wrong. what should be the syntax after keyword AS?

Best Answer

What you are looking for is to actually do the INSERT into warehouse_maine after Warehouse

DELIMITER $$
DROP TRIGGER IF EXISTS Warehouse_InsertAfter $$
CREATE TRIGGER Warehouse_InsertAfter AFTER INSERT ON Warehouse
FOR EACH ROW
BEGIN
    IF NEW.state = 'maine' THEN
        INSERT IGNORE INTO warehouse_maine (shipperid, name, state)
        VALUES (NEW.shipperid, NEW.name, NEW.state);
    END IF;
END;
DELIMITER ;

This trigger will do the job. If you are doing a bulk INSERT of rows into the Warehouse table, this trigger will slow things down a bit. If you plan to do such bulk INSERTs, you are better off doing that in a single INSERT:

INSERT IGNORE INTO warehouse_maine
SELECT * FROM Warehouse WHERE state='maine';

Related Solutions

phpMyAdmin – Trigger Syntax

It may be more efficient to simply use a cascading delete via a foreign key. When a row with the same product_code is deleted from products, it will be deleted in sales_detail as well.

ALTER TABLE sales_details
ADD FOREIGN KEY fk_product_code(product_code)
REFERENCES products(product_code)
ON DELETE CASCADE;

But if you'd like to use a trigger to delete rows from the products table that have the same product_code as the row deleted, this should do the trick:

 delimiter // 
 CREATE TRIGGER removeforeing 
  BEFORE DELETE ON products
  FOR EACH ROW BEGIN
    DELETE FROM sales_details WHERE product_code = OLD.product_code;
 END// 
 delimiter ;

Edit

Per documentation,

Within the trigger body, you can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.

SQL Server Triggers – INSTEAD OF INSERT vs. FOR INSERT

My question is why insert trigger is not fired from instead of trigger? It should because it is also using insert query in it. Why TestTable get the new inserted row?

The INSTEAD OF trigger replaces the original insert. So the code:

Insert into dbo.TestTable (Name,Age,DOB,Address) 
Select Name,Age,DOB,Address From inserted;

...is executed instead of the original insert:

Insert into dbo.TestTable values ('Waleed',24,'10-2-2015','Lahore');

The actions in the instead of trigger are completed before constraints are checked, and before any AFTER triggers fire.

When the instead of trigger completes, a row has been added to the TestTable and CopyTable tables. Note though that the transaction is still active at this point, so the changes are not yet permanent.

Next, the after trigger fires. It sees the name 'Waleed' in the inserted table, prints a message, but does not rollback the changes made so far in the transaction (including the rows added by the instead of trigger). No row is added to the AuditTable.

If the condition that checked for the name 'Waleed' also performed a rollback, everything would be rolled back, and no rows would be added to any table.

Best Answer

Related Solutions

phpMyAdmin – Trigger Syntax

SQL Server Triggers – INSTEAD OF INSERT vs. FOR INSERT

Related Question