Jd9224Kc

1-- Transactions.sql
2-- Defines the table structure for transactions
3
4drop table if exists transactions;
5create table transactions
6(
7	transaction_id int not null auto_increment,
8	account_id int not null,
9	title varchar(255) not null,
10	
11	credit decimal(10, 2) not null,
12	debit decimal(10, 2) not null,
13	
14	create_date datetime null,
15	update_date datetime null,
16	statement_date datetime null,
17	delete_date datetime null,
18	
19	primary key(transaction_id)	
20);
21
22-- Create triggers to update the create_date column
23drop trigger if exists create_date_insert_transactions_trigger;
24create trigger create_date_insert_transactions_trigger before insert on transactions
25for each row set new.create_date = NOW();
26
27drop trigger if exists update_date_update_transactions_trigger;
28create trigger create_date_update_transactions_trigger before update on transactions
29for each row set new.update_date = NOW();
30
31-- Create triggers to update account balances!
32
33drop trigger if exists update_account_balances_insert_trigger;
34DELIMITER $$
35create trigger update_account_balances_insert_trigger 
36after insert on transactions
37for each row begin	
38	UPDATE accounts SET accounts.balance = COALESCE((SUM(transactions.credit) - SUM(transactions.debit)), 0.00)
39	WHERE accounts.account_id = new.account_id;
40END;
41$$
42
43
44delimiter ;
45drop trigger if exists update_account_balances_update_trigger;
46DELIMITER $$
47create trigger update_account_balances_update_trigger 
48after update on transactions
49for each row begin	
50	UPDATE accounts SET accounts.balance = COALESCE((SUM(transactions.credit) - SUM(transactions.debit)), 0.00)
51	WHERE accounts.account_id = new.account_id;
52END;
53$$
54delimiter ;
55
56insert into transactions (account_id, title, credit, debit) VALUES
57	(1, "[[Opening Balance]]", 1000.00, 0.00)