7XZPBwSv

1CREATE TABLE `users` (
2  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
3  `email` varchar(45) DEFAULT NULL,
4  `username` varchar(16) DEFAULT NULL,
5  `salt` varchar(16) DEFAULT NULL,
6  `password` varchar(128) DEFAULT NULL,
7  `lastlogin` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
8  `joined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
9  `loggedin` tinyint(1) unsigned NOT NULL DEFAULT '0',
10  `sessionkey` varchar(60) DEFAULT NULL,
11  `verifycode` varchar(16) DEFAULT NULL,
12  `verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
13  `banned` tinyint(1) unsigned NOT NULL DEFAULT '0',
14  `locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
15  `ip_address` varchar(45) DEFAULT NULL,
16  `failedattempts` tinyint(1) unsigned NOT NULL DEFAULT '0',
17  `unlocktime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
18  PRIMARY KEY (`id`)
19) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
20	
21CREATE TABLE `user_records` (
22  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
23  `userid` int(8) unsigned DEFAULT NULL,
24  `action` varchar(100) DEFAULT NULL,
25  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
26  PRIMARY KEY (`id`)
27) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
28	
29USE `gknet`;
30DELIMITER $$
31CREATE DEFINER=`root`@`localhost` TRIGGER `before_create_user` BEFORE INSERT ON `users` FOR EACH ROW BEGIN
32INSERT INTO user_records (action, userid, timestamp)
33  VALUES ('CREATED', ID, NOW() );
34END
35	
36use `gknet`;
37
38delimiter $$
39
40drop trigger if exists before_create_user; $$
41
42create definer=`root`@`localhost` trigger `before_create_user` 
43       before insert on `users` 
44for each row begin
45  declare fk_parent_user_id int default 0;
46
47  select auto_increment into fk_parent_user_id
48    from information_schema.tables
49   where table_name = 'users'
50     and table_schema = database();
51
52  insert into user_records ( action, userid, timestamp )
53         values ( 'created', fk_parent_user_id, now() );
54end;
55
56$$
57
58delimiter ;
59	
60USE `gknet`;
61DELIMITER $$
62CREATE DEFINER=`root`@`localhost` 
63TRIGGER `after_create_user` AFTER INSERT ON `users` 
64FOR EACH ROW 
65BEGIN
66INSERT INTO user_records (action, userid, timestamp)
67  VALUES ('CREATED', NEW.ID, NOW() );
68END; $$