SQL Trigger |
Post Reply |
Author | |
TechSquared
Newbie Joined: 01 March 2006 Location: United States Status: Offline Points: 15 |
Post Options
Thanks(0)
Posted: 05 December 2007 at 5:07pm |
I wanted to share a trigger that I installed that has helped keep my quarantine database manageable and see if anyone has a better idea to help make the quarantine smaller.
I keep the quarantined email for 10 full days, in case someone is on vacation. My sql database is around 1 gig.
The basic idea is to instantly (or close to it) delete any email quarantined that is not associated with a known user.
The trigger I use checks the login table to see if the user exists, otherwise it marks it for deletion.
IF (SELECT COUNT(*) FROM [dbo].[tblLogins], [inserted]
WHERE [dbo].[tblLogins].[EMail] = [inserted].[EmailTo]) < 1 BEGIN UPDATE [dbo].[tblQuarantine] SET [Expire] = 1 WHERE [QuarID] = @@IDENTITY END Please let me know if you have a better way so I can make the database even smaller.
|
|
Desperado
Senior Member Joined: 27 January 2005 Location: United States Status: Offline Points: 1143 |
Post Options
Thanks(0)
|
There are many ways to skin a cat (what a horrible expression!). However, at first glance, this should work.
I actually use something more along the line of:
select count(*) from tblQuarantine
where EmailTo not in (select Email from tblLogins) But mine get even more complex as I have users that manage muntiple addresses and have a generic login like administrator@domain.com. Edited by Desperado - 05 December 2007 at 8:59pm |
|
The Desperado
Dan Seligmann. Work: http://www.mags.net Personal: http://www.desperado.com |
|
TechSquared
Newbie Joined: 01 March 2006 Location: United States Status: Offline Points: 15 |
Post Options
Thanks(0)
|
If you don't mind my asking, what do you use to combine those accounts into a single user able to check the junk mail. I figured I could do that though my own customization, but is there an easy way without disturbing the rest of the software? |
|
Desperado
Senior Member Joined: 27 January 2005 Location: United States Status: Offline Points: 1143 |
Post Options
Thanks(0)
|
OK ... It is not easy but not very hard either. My asp gui does all the work. I did, however add a table to link the "special" users to the domains/accounts they can manage. I can give you more information by PM but not today as I am in the middle of my 32nd hour straight without sleep and am very fuzzy. Remind me sometime.
|
|
The Desperado
Dan Seligmann. Work: http://www.mags.net Personal: http://www.desperado.com |
|
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |
This page was generated in 0.156 seconds.