On 02/10/2012 05:34 AM, 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
Nice work, Bharath. I'm happy to see someone so proficient with mysql in
our community. Would you care to suggest these improvements on the
vpopmail list perhaps? (vch...@inter7.com)
Thanks.
--
-Eric 'shubes'
---------------------------------------------------------------------------------
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
