On 02/10/12 19:34, Bharath Chari wrote:
On Friday 10 February 2012 11:35 AM, patr...@aofh.us wrote:
4) Exit the mysql shell. On creating a new ID in the domain
example.com,
you should get the ID and creation date/time in the table
log_example_com.
Just tried it, works perfectly!
Good to know!
Now for a more complete version which logs creation and deletion date.
It's not as simple as writing a trigger for delete, because a single
user id can be created/deleted any number of times and we wouldn't
know which record to update. Since this is kind of an auditing table,
we would like to track each instance of creation/deletion.
So, there are a few modifications to be made to the domain table in
the vpopmail database.
Note: I have just tried it on a production server and it worked
without any problem for me, but please make a backup of the table
first or try it on a dummy domain.
Assumption: Domain to be monitored is example.com and the domain
table is example_com.
1) Log in to mysql shell as root
mysql -uroot -p vpopmail
2) Alter the example_com table (replace example_com with your domain
table)
ALTER TABLE `example_com` DROP PRIMARY KEY;
ALTER TABLE `example_com` ADD UNIQUE (`pw_name`);
ALTER TABLE `example_com` ADD `uid` INT NOT NULL AUTO_INCREMENT
PRIMARY KEY FIRST ;
3) Create log table - replace example_com with your domain table name
CREATE TABLE `userlog_example_com` (
`uid` int(11) NOT NULL,
`pw_name` varchar(255) NOT NULL,
`creation_date` datetime NOT NULL default '0000-00-00 00:00:00',
`deletion_date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
4) Import existing records into the userlog_example_com table. While
this won't help with creation date, it will allow you to track when an
ID was deleted:
mysql>insert into userlog_example_com (uid,pw_name) select uid,pw_name
from example_com;
5) Create the INSERT Trigger (again replace example_com as appropriate)
mysql> delimiter $$
mysql> create trigger example_com_insert_trigger
-> AFTER insert on example_com
-> FOR EACH ROW
-> BEGIN
-> insert into userlog_example_com
values(new.uid,new.pw_name,NOW(),'0000-00-00 00:00:00');
-> END$$
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
6) Create the DELETE Trigger (again replace example_com as appropriate)
mysql> delimiter $$
mysql> CREATE TRIGGER example_com_delete_trigger
-> AFTER delete on example_com
-> FOR EACH ROW
-> BEGIN
-> update userlog_example_com set deletion_date=NOW() where
uid=old.uid;
-> END$$
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql>quit
7) Test and test again:
Create a test user eg : xxxxx using qmailadmin
Delete the user xxxxx
Create the test user xxxxx again
Delete the user xxxxx
Your table userlog_example_com should contain two records both with
xxxxx as the pw_name but with different uid numbers and
creation/deletion dates.
Hope this helps!
Bharath
Thank you very much Bharath, I really forgot about MySQL trigger feature
I'll try this on development server first then I'll update the Wiki for it.
---------------------------------------------------------------------------------
Qmailtoaster is sponsored by Vickers Consulting Group
(www.vickersconsulting.com)
Vickers Consulting Group offers Qmailtoaster support and installations.
If you need professional help with your setup, contact them today!
---------------------------------------------------------------------------------
Please visit qmailtoaster.com for the latest news, updates, and packages.
To unsubscribe, e-mail: qmailtoaster-list-unsubscr...@qmailtoaster.com
For additional commands, e-mail: qmailtoaster-list-h...@qmailtoaster.com
