Shulou(Shulou.com)06/01 Report--

MYSQL:

Create a trigger to automatically save the deleted data to a backup table when deleting a table, and add the deletion time:

Create a backup table:

CREATE TABLE `products_ `(

Prod_id int (11) NOT NULL

Vend_id int (11) NOT NULL

Prod_name char (20)

Prod_price int (11)

Prod_desc char (50)

Del_time datetime

PRIMARY KEY (`prod_ id`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk

Create a trigger:

DROP TRIGGER prod_delete;-Delete trigger prod_delete, if present. To modify the trigger, you can only delete the rebuild.

DELIMITER $$--redefine the name Terminator

CREATE TRIGGER prod_delete-trigger is prod_delete

BEFORE DELETE ON products-takes effect before deleting products table contents

FOR EACH ROW-for all deleted rows

BEGIN-start trigger content

INSERT INTO products_ (prod_id,vend_id,prod_name,prod_price,prod_desc,del_time) VALUES (old.prod_id,old.vend_id,old.prod_name,old.prod_price,old.prod_desc,NOW ())

END;-- null end inside the trigger

$--redefine the name Terminator

DELIMITER;-- redefine the name Terminator

MSSQL:

When updating the material inventory, automatically update the real-time inventory column of the BOM entry table:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create TRIGGER ICInventory_update-create an ICInventory_update trigger and modify it with alter

ON ICInventory-based on inventory tables

AFTER update-acting after inventory update

AS

Begin-start the action

Update ICBOMChild set FEntrySelfZ0142=Inserted.fqty from Inserted where ICBOMChild.FItemID=Inserted.FItemID and inserted.FStockID='4435'

End-- end

GO-run

The temporary tables and formats of MYSQL and MSSQL are different. Mysql has only new tables that update data and old tables that delete data. The updated table of MSSQL is called Inserted, and the temporary table that stores deleted data is named deleted.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.