Mega Code Archive
Creating the UpdateLog table and an update trigger on the authors table
<![CDATA[
4>
5>
6> CREATE TABLE authors(
7> au_id varchar(11),
8> au_lname varchar(40) NOT NULL,
9> au_fname varchar(20) NOT NULL,
10> phone char(12) NOT NULL DEFAULT ('UNKNOWN'),
11> address varchar(40) NULL,
12> city varchar(20) NULL,
13> state char(2) NULL,
14> zip char(5) NULL,
15> contract bit NOT NULL
16> )
17> GO
1> insert authors values('1', 'Joe', 'Abra', '111 111-1111', '6 St.', 'Berkeley', 'CA', '11111', 1)
2> insert authors values('2', 'Jack', 'Majo', '222 222-2222', '3 St.', 'Oakland' , 'CA', '22222', 1)
3> insert authors values('3', 'Pink', 'Cherry', '333 333-3333', '5 Ln.', 'Vancouver', 'BC', '33333', 1)
4> insert authors values('4', 'Blue', 'Albert', '444 444-4444', '7 Av.', 'Vancouver', 'BC', '44444', 1)
5> insert authors values('5', 'Red', 'Anne', '555 555-5555', '6 Av.', 'Regina', 'SK', '55555', 1)
6> insert authors values('6', 'Black', 'Michel', '666 666-6666', '3 Pl.', 'Regina', 'SK', '66666', 1)
7> insert authors values('7', 'White', 'Sylvia', '777 777-7777', '1 Pl.', 'Rockville', 'MD', '77777', 1)
8> insert authors values('8', 'Yellow','Heather','888 888-8888', '3 Pu', 'Vacaville', 'CA', '88888', 0)
9> insert authors values('9', 'Gold', 'Dep', '999 999-9999', '5 Av.', 'Oakland', 'CA', '99999', 0)
10> insert authors values('10', 'Siler', 'Dean', '000 000-0000', '4 Av.', 'Oakland', 'CA', '00000', 1)
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE TABLE UpdateLog
3> (
4> table_name VARCHAR(30) NOT NULL,
5> column_name VARCHAR(30) NOT NULL,
6> updated_by VARCHAR(30) NOT NULL,
7> updated_when DATETIME NOT NULL
8> )
9> GO
1>
2> CREATE TRIGGER trAuthors_Upd ON Authors
3> FOR UPDATE
4> AS
5> DECLARE @intRowCount int
6> SELECT @intRowCount = @@RowCount
7> IF @intRowCount > 0
8> BEGIN
9> IF UPDATE(au_id)
10> INSERT UpdateLog VALUES ('authors', 'au_id', suser_name(), getdate())
11> IF UPDATE(au_lname)
12> INSERT UpdateLog VALUES ('authors', 'au_lname', suser_name(), getdate())
13> IF UPDATE(au_fname)
14> INSERT UpdateLog VALUES ('authors', 'au_fname', suser_name(), getdate())
15> IF UPDATE(phone)
16> INSERT UpdateLog VALUES ('authors', 'phone', suser_name(), getdate())
17> IF UPDATE(address)
18> INSERT UpdateLog VALUES ('authors', 'address', suser_name(), getdate())
19> IF UPDATE(city)
20> INSERT UpdateLog VALUES ('authors', 'city', suser_name(), getdate())
21> IF UPDATE(state)
22> INSERT UpdateLog VALUES ('authors', 'state', suser_name(), getdate())
23> IF UPDATE(zip)
24> INSERT UpdateLog VALUES ('authors', 'zip', suser_name(), getdate())
25> IF UPDATE(contract)
26> INSERT UpdateLog VALUES ('authors', 'contract', suser_name(), getdate())
27> END
28> GO
1>
2>
3> drop trigger trAuthors_Upd;
4> GO
1>
2> drop table sales;
3> drop table UpdateLog;
4> GO
]]>