Examples and Notes by Week

↑ Up to Parent directory
View Create_dorepeat_proc.sql code.	Open/Save Create_dorepeat_proc.sql.
View Create_hello_func.sql code.	Open/Save Create_hello_func.sql.
View Create_ins_sum_trig.sql code.	Open/Save Create_ins_sum_trig.sql.
View event_ts.sql code.	Open/Save event_ts.sql.
View show_events.sql code.	Open/Save show_events.sql.
View testref_trig.sql code.	Open/Save testref_trig.sql.

The code for Create_ins_sum_trig.sql is:
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;