Trigger
========================Insert =========================
mysql> create table table1(
-> var1 int(3) NOT NULL,
-> var2 int(3) NOT NULL,
-> sum int(3) NULL
-> );
Query OK, 0 rows affected (0.52 sec)
mysql>
mysql> Create trigger updateSumTable1
-> Before insert
-> On table1
-> For each row
-> Set new.sum=new.var1+new.var2;
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> insert into table1 values(3,6,null);
Query OK, 1 row affected (0.08 sec)
mysql> select * from table1;
+------+------+------+
| var1 | var2 | sum |
+------+------+------+
| 3 | 6 | 9 |
+------+------+------+
1 row in set (0.10 sec)
========================Delete=========================
mysql> create table table2(
-> var1 int(3) NOT NULL,
-> var2 int(3) NOT NULL,
-> sum int(3) NULL
-> );
Query OK, 0 rows affected (0.52 sec)
mysql> Create trigger updateTable2
-> After delete-> On table1
-> For each row
-> insert into table2 values(old.var1,old.var2,old.sum);
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> select * from table2;
Empty set (0.00 sec)
mysql>
mysql> delete from table1 where var1=3;
Query OK, 1 row affected (0.11 sec)
mysql> select * from table1;
Empty set (0.00 sec)
mysql> select * from table2;
+------+------+------+
| var1 | var2 | sum |
+------+------+------+
| 3 | 6 | 9 |
+------+------+------+
1 row in set (0.00 sec)
========================UPDATE=========================
mysql> create table table3(
-> var int NOT NULL,
-> sum int NULL
-> );
Query OK, 0 rows affected (0.44 sec)
mysql> DELIMITER //
mysql> Create trigger updateSumTable3
-> Before update
-> On table3
-> For each row
-> begin
-> if(new.var>old.var) then
-> Set new.sum=old.sum+new.var;
-> end if;
-> END //
Query OK, 0 rows affected (0.13 sec)
mysql> DELIMITER ;
mysql> insert into table3 values(3,3);Query OK, 1 row affected (0.12 sec)
mysql> select * from table3;
+-----+------+
| var | sum |
+-----+------+
| 3 | 3 |
+-----+------+
1 row in set (0.00 sec)
mysql> update table3 set var=5 where var=3;
Query OK, 1 row affected (0.18 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from table3;
+-----+------+
| var | sum |
+-----+------+
| 5 | 8 |
+-----+------+
1 row in set (0.00 sec)
mysql> update table3 set var=2 where var=5;
Query OK, 1 row affected (0.36 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from table3;
+-----+------+
| var | sum |
+-----+------+
| 2 | 8 |
+-----+------+
1 row in set (0.00 sec)
');
1 comment:
Thanks for sharing your informative. TechPotli, we stock a curated collection of routers, outdoor CPEs, link radios, surge protectors, and much more. In this post, let’s deep dive into popular devices, price ranges, and setup tips
Gigabit Ethernet Surge Suppressor
Ubiquiti router for home price in delhi
Mikrotik powerbox rb750p pbr2 price
biometric device price in delhi
Post a Comment