Sql-server – MS SQL Creating Tables and Adding Values by a Trigger

insertsql servertabletriggerupdate

Table_A is an existing table which has 3 columns Id (PK), Name, Stock.
I would like to create a trigger to create a table.

For example, Table_A has an item, Id (PK) = 3, Name = Apple, Stock = 10.
By using triggers I would like to create a new table called Table_B and when I write the following code "INSERT INTO Table_B VALUES(3,5)" a new table will be created and it will have 2 columns ProductId and Quanity. At the same time the value for Stock for Table_A will be decreased by 5. Every time I insert something to Table_B I would like them to get added to Table_B and the stock in Table_A to be decreased by the quantity of that item inserted with the related ProductId. Thanks!

Best Answer

If you have a [Product] table and a [ProductTransaction] table, and your goal is to have [Product].[Quantity] always be the sum of the transaction table, then your trigger will need to take into account INSERT, UPDATE, and DELETE operations. You will also need to ensure that nobody ever directly updates the [Product].[Quantity] value, as it will result in inconsistencies.

One way to get around this without using a trigger would be to use a view. For example:

CREATE OR REPLACE VIEW vw_inventory AS
SELECT p.[ID], p.[Name], SUM(txn.[Quantity]) AS [Quantity]
  FROM [Product] p INNER JOIN [ProductTransaction] txn ON p.[ID] = txn.[ProductID]
 GROUP BY p.[ID], p.[Name]

Then you can query off the view:

SELECT * FROM [vw_inventory]
 WHERE [ID] = 4;

Otherwise, if you really want to use a trigger, then you'll need to set an AFTER trigger on the transaction table:

CREATE TRIGGER [trg_product_quantity]
    ON [ProductTransaction]
 AFTER INSERT, UPDATE, DELETE
    AS
 BEGIN
    SET NOCOUNT ON;
    UPDATE [Product] p 
       SET [Quantity] = tmp.[Quantity]
      FROM (SELECT z.[ProductID], SUM(z.[Quantity]) as [Quantity]
              FROM [ProductTransaction] txn 
             WHERE txn.[ProductID] IN (inserted.[ProductID], deleted.[ProductID])
             GROUP BY z.[ProductID]) tmp 
     WHERE tmp.[ProductID] = p.[ID];
   END

Note: Be sure to double-check and modify this SQL code. I may have mixed in a little MySQL/PostgreSQL by mistake.

Hope this gives you something to think about ??

Related Solutions

Mysql – Keeping 2 tables in sync

If you have this scenario

table_a has an auto_increment field
table_b and table_c have table structure identical to table_a
You are running MySQL 5.1+

you can create a Stored Procedure and a MySQL event instead of a Trigger

For the sake of example, let's say table_a looks like this:

CREATE TABLE table_a
(
    id not null auto_increment,
    ....
    primary key (id),
);

Create a Stored Procedure on the Slave to Copy Data to table_b and table_c

DELIMITER $$ 
DROP PROCEDURE IF EXISTS `Load_New_Data` $$ 
CREATE PROCEDURE `Load_New_Data` () 
BEGIN
    DECLARE min_id,min_idb,mid_idc INT;

    SELECT MAX(id) INTO min_idb FROM table_b;
    SELECT MAX(id) INTO min_idc FROM table_c;
    SET min_id = IF(min_idb < min_idc,min_idb,min_idc);

    DROP TABLE IF EXISTS table_a_delta;
    CREATE TABLE table_a_delta SELECT * FROM table_a WHERE 1=2;
    INSERT INTO table_a_delta SELECT * FROM table_a WHERE id >= min_id;
    INSERT IGNORE INTO table_b SELECT * FROM table_a_delta;
    INSERT IGNORE INTO table_c SELECT * FROM table_a_delta;

    DROP TABLE table_a_delta;
END $$ 
DELIMITER ;

Then, schedule a MySQL Event on the Slave for the Stored Procedure to go off every 5 minutes starting 5 minutes after you create the event:

CREATE EVENT ev 
    ON SCHEDULE 
      EVERY 5 MINUTE 
      STARTS (NOW() + INTERVAL 5 MINUTE) 
    DO 
      CALL Load_New_Data();

Don't forget to Add This to the Slave's /etc/my.cnf

[mysqld]
event_scheduler=1

Mysql – create trigger which inserts rows depending on enum values

It's not pretty, but this gets the possible enum values from INFORMATION_SCHEMA.COLUMNS.

DELIMITER $$

DROP TRIGGER ins_addresses $$

CREATE TRIGGER ins_addresses AFTER INSERT on searcharticles
FOR EACH ROW
BEGIN

DECLARE column_list char(200);
DECLARE current_value char(200);
DECLARE counter INT DEFAULT 0;
DECLARE num_enums INT;

  /* Format goes from "enum('shop1','shop2')" to "shop1,shop2" */

  SELECT REPLACE(SUBSTR(COLUMN_TYPE,6,LENGTH(COLUMN_TYPE)-6),'''','')
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_SCHEMA = 'stackex' 
  AND TABLE_NAME = 'addresses' 
  AND COLUMN_NAME = 'shop'
  INTO column_list;

  SET num_enums = LENGTH(column_list)- LENGTH(REPLACE(column_list, ',', ''))+1;

  WHILE counter < num_enums DO
    SET current_value = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(column_list, ',', counter+1),
          LENGTH(SUBSTRING_INDEX(column_list, ',', counter )) + 1), ',', ''));

    INSERT INTO addresses(articleID, shop, createtime)
    VALUES (NEW.articleID, current_value, CURRENT_TIMESTAMP);

    SET counter = counter + 1;  
  END WHILE;
END$$
DELIMITER ;

Test:

mysql> use stackex;
Database changed
mysql> insert into searcharticles values ( 1, 'foo','bar' );
Query OK, 1 row affected (0.00 sec)

mysql> select * from addresses;
+-----------+-------+------+--------+---------------------+------------+
| articleID | shop  | url  | status | createTime          | updateTime |
+-----------+-------+------+--------+---------------------+------------+
|         1 | shop1 | NULL |      0 | 2012-08-17 09:32:33 |       NULL |
|         1 | shop2 | NULL |      0 | 2012-08-17 09:32:33 |       NULL |
+-----------+-------+------+--------+---------------------+------------+
2 rows in set (0.00 sec)

mysql>

I actually hate myself for answering this! Change the data model to use a lookup table, shops.

It'll break horribly if the shop enum names have commas or quotation marks in them (probably).

Best Answer

Related Solutions

Mysql – Keeping 2 tables in sync

Mysql – create trigger which inserts rows depending on enum values

Related Question