VwicGetJ

1import sqlite3
2import pprint
3conn = sqlite3.connect('BOOKS.sqlite')
4c = conn.cursor()
5pp = pprint.PrettyPrinter(indent=5, width=100, compact=False)
6
7#c.execute(''' drop table if exists A ''')
8c.execute(''' drop trigger if exists InsItems ''')
9c.execute(''' drop trigger if exists DelItems ''')
10c.execute(''' drop trigger if exists InsItems2 ''')
11c.execute(''' drop trigger if exists InsItems2Raise ''')
12
13c.execute('''
14create table if not exists A (
15A_id_old integer,
16A_id_new integer,
17A_contract_old integer,
18A_contract_new integer,
19A_n_old integer,
20A_n_new integer,
21A_date text not null,
22operation text not null)
23''')
24
25c.execute('''
26create trigger if not exists InsItems
27after insert on items begin
28insert into A values (null, new.i_id, null, new.i_contract, null, new.i_count, datetime('now'), 'insert');
29end
30''')
31
32c.execute('''
33create trigger if not exists DelItems
34after delete on items begin
35insert into A values (old.i_id, null, old.i_contract, null, old.i_count, null, datetime('now'), 'delete');
36end
37''')
38
39c.execute('''
40create trigger if not exists UpdItems
41after update on items begin
42insert into A values (old.i_id, null, old.i_contract, null, old.i_count, null, datetime('now'), 'update');
43end
44''')
45
46c.execute('''
47create trigger if not exists InsItems2
48after insert on items begin
49update books
50set b_rest = b_rest - new.i_count
51where b_contract = new.i_contract;
52end
53''')
54
55
56c.execute('''
57create trigger if not exists InsItems2Raise
58before insert on items begin
59Select raise('Большое количество книг')
60from books
61where (b_rest - new.i_count < 0) and (b_contract = new.i_contract);
62end
63''')
64
65items = [(29, 3, 100), (14, 5, 150), (12, 7, 200), (20, 2, 150)]
66#c.executemany('''INSERT INTO items VALUES(?,?,?)''', items)
67conn.commit()
68
69c.execute('''
70update items
71set i_id = 100
72where i_id = 4
73''')
74
75c.execute('''
76Select *
77From items
78''')
79pp.pprint(c.fetchall())
80
81c.execute('''
82Select *
83From A
84''')
85pp.pprint(c.fetchall())
86
87c.execute('''
88Select *
89From books
90''')
91pp.pprint(c.fetchall())
92
93conn.close()