| Server IP : 134.29.175.74 / Your IP : 216.73.216.160 Web Server : nginx/1.10.2 System : Windows NT CST-WEBSERVER 10.0 build 19045 (Windows 10) i586 User : Administrator ( 0) PHP Version : 7.1.0 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/nginx/html/MichaelMalz/CST1602/Resources/Week/09/ |
Upload File : |
DROP TABLE IF EXISTS account;
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
DROP TRIGGER IF EXISTS ins_sum;
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
SET @sum = 0;
INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-500.00);
SELECT @sum AS 'Total amount inserted';
DROP TRIGGER IF EXISTS ins_transaction;
CREATE TRIGGER ins_transaction BEFORE INSERT ON account
FOR EACH ROW PRECEDES ins_sum
SET
@deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
@withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
SET @sum = 0;
SET @deposits = 0;
SET @withdrawals = 0;
INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-500.00);
SELECT @sum AS 'Total amount inserted';
SELECT @deposits AS 'deposits', @withdrawals AS 'withdrawals';
DROP TRIGGER IF EXISTS upd_check;
delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END;//
delimiter ;
UPDATE account SET amount = 400 WHERE acct_num = 137;
UPDATE account SET amount = 14.98 WHERE acct_num = 137;