HbWzD561

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