| Author |
 Topic  |
|
|
philsbbs
Junior Member
United Kingdom
397 Posts |
 Posted - 27 June 2007 : 19:24:49
|
Hi,
I want to copy the members from members_pending to members_pending_deleted before they get deleted.
So I have a backup in case I need it.
I've found the delete code.
==
elseif strAction = "delete" then
if selID = "-1" then
'## Forum_SQL - Delete the Member
strSql = "DELETE FROM " & strMemberTablePrefix & "MEMBERS_PENDING "
strSql = strSql & " WHERE M_STATUS = " & 0
strSql = strSql & " AND M_LEVEL = " & -1
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
Response.Write " <br /><p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strHeaderFontSize & """><b>Members Deleted!</b></font></p>" & vbNewLine & _
" <meta http-equiv=""Refresh"" content=""2; URL=admin_accounts_pending.asp"">" & vbNewLine & _
" <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strHeaderFontSize & """>All pending members have been deleted!</font></p>" & vbNewLine & _
" <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """><a href=""admin_accounts_pending.asp"">Back To Members Pending</font></a></p>" & vbNewLine
WriteFooter
Response.End
==
What is the sql code to copy it before it's deleted.
Thanks in advance.
|
Phil |
|
|
Jezmeister
Senior Member
United Kingdom
1141 Posts |
Posted - 27 June 2007 : 19:34:01
|
| you should be able to just c & p the insert code to the members table for when you approve members and change the table name, you'll have to add a new action for when you permanently delete a member and use the code that's currently there for that. |
 |
|
|
gary b
Junior Member
USA
267 Posts |
Posted - 28 June 2007 : 08:46:03
|
quote:
I want to copy the members from members_pending to members_pending_deleted before they get deleted.
So I have a backup in case I need it.
Of course, that implies that you have provisions to retrieve the data... 'in case you need it'. Without a method to retrieve the data, I question the usefulness of the change. [Unless you access the database directly via Access]
Working for government, I learned that we RARELY *delete* data. There is some merit to this position. Although it would require changing the forum code, you may want to consider this idea.
1. Add a 'deleted' Y/N field to the members_pending table (acts as a 'flag')
2. Revise Admin member processing page (admin_members_pending?) to enter 'True' for [deleted] field and NOT actually delete data
3. Revise Admin member processing page to include 'where' clause to display pending members. [WHERE FORUM_MEMBERS_PENDING.deleted = False]
Hopefully this conveys my approach. You would still have to make provisions to 'clean up' this table from time to time. Since I copy the forum database to local HDD (for backup) periodically, I would clean up the table directly in Access. Copy database back to server and you are current with forum and backup.
Your mileage may vary... 
Edit: Fix typo |
Edited by - gary b on 28 June 2007 08:47:20 |
|
|
|
philsbbs
Junior Member
United Kingdom
397 Posts |
Posted - 28 June 2007 : 14:50:48
|
I was thinking more off :-
insert into pending_members_deleted
select *
etc
Any others ideas, code examples.
|
Phil |
|
|
|
gary b
Junior Member
USA
267 Posts |
Posted - 28 June 2007 : 17:45:49
|
Allow me to add one point to my approach...
By employing a 'flag', you can (as Admin M_LEVEL=3) modify the page so YOU can see both 'deleted' and current pending members. [Or create a clone of admin_members_pending that will do this.] The beauty of this is that -- if you want to UNdelete a record -- simply uncheck the flag (set = False).
I use this approach often in my databases. I believe code changes would be minimal using this method.
FWIW... |
|
|
|
philsbbs
Junior Member
United Kingdom
397 Posts |
Posted - 28 June 2007 : 18:00:25
|
I fully understand and agree with you but at this stage my change is more for monitor purposes, bit of a long story.
Could you help with the sql for my needs. |
Phil |
|
|
|
gary b
Junior Member
USA
267 Posts |
Posted - 28 June 2007 : 22:04:46
|
philsbbs...
What is your timetable? Due in two weeks? Crisis -- need it now???
Like everyone else, 'time' is in short supply. But given a week, I might well have something for you...
quote:
I want to copy the members from members_pending to members_pending_deleted before they get deleted.
That's all, correct??
|
|
|
|
philsbbs
Junior Member
United Kingdom
397 Posts |
Posted - 29 June 2007 : 02:24:00
|
I can wait two weeks.
Thanks in advance for your help. |
Phil |
|
|
|
philsbbs
Junior Member
United Kingdom
397 Posts |
Posted - 30 June 2007 : 05:58:10
|
Solution found, thanks to Rui.
Trigger added to sql server.
=
CREATE TRIGGER del_trigger ON FORUM_MEMBERS_PENDING
FOR DELETE
AS
INSERT INTO FORUM_MEMBERS_PENDING_DELETED
SELECT * FROM DELETED
=
Please note in order for this to work for me I had to change some fields from ntext to nvarchar(4000).
So my solution might not be suitable for you.
Thanks Rui for all your help.
Your a true asset to Snitz.
|
Phil |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
gary b
Junior Member
USA
267 Posts |
Posted - 01 July 2007 : 13:38:30
|
Geesh!! That seems too simple. Hadn't check this thread until I had already made code changes.  So -- for whatever it's worth -- here are changes necessary to admin_accounts_pending page:
In an UNmodified file, replace lines 187 to and including 219 with following code:
elseif strAction = "delete" then
if selID = "-1" then
'## Forum_SQL - Add the Members to Member_Deleted table
strSqldel = "INSERT INTO FORUM_MEMBERS_DELETED ( MEMBER_ID, M_STATUS, M_NAME, M_USERNAME, M_PASSWORD, M_EMAIL, M_COUNTRY, "
strSqldel = strSqldel & "M_HOMEPAGE, M_SIG, M_VIEW_SIG, M_SIG_DEFAULT, M_DEFAULT_VIEW, M_LEVEL, M_AIM, M_ICQ, M_MSN, M_YAHOO, M_POSTS, "
strSqldel = strSqldel & "M_DATE, M_LASTHEREDATE, M_LASTPOSTDATE, M_TITLE, M_SUBSCRIPTION, M_HIDE_EMAIL, M_RECEIVE_EMAIL, M_LAST_IP, M_IP, "
strSqldel = strSqldel & "M_FIRSTNAME, M_LASTNAME, M_OCCUPATION, M_SEX, M_AGE, M_DOB, M_HOBBIES, M_LNEWS, M_QUOTE, M_BIO, M_MARSTATUS, "
strSqldel = strSqldel & "M_LINK1, M_LINK2, M_CITY, M_STATE, M_PHOTO_URL, M_KEY, M_NEWEMAIL, M_PWKEY, M_APPROVE, M_SHA256 ) "
strSqldel = strSqldel & "SELECT FORUM_MEMBERS_PENDING.MEMBER_ID, FORUM_MEMBERS_PENDING.M_STATUS, FORUM_MEMBERS_PENDING.M_NAME, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_USERNAME, FORUM_MEMBERS_PENDING.M_PASSWORD, FORUM_MEMBERS_PENDING.M_EMAIL, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_COUNTRY, FORUM_MEMBERS_PENDING.M_HOMEPAGE, FORUM_MEMBERS_PENDING.M_SIG, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_VIEW_SIG, FORUM_MEMBERS_PENDING.M_SIG_DEFAULT, FORUM_MEMBERS_PENDING.M_DEFAULT_VIEW, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_LEVEL, FORUM_MEMBERS_PENDING.M_AIM, FORUM_MEMBERS_PENDING.M_ICQ, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_MSN, FORUM_MEMBERS_PENDING.M_YAHOO, FORUM_MEMBERS_PENDING.M_POSTS, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_DATE, FORUM_MEMBERS_PENDING.M_LASTHEREDATE, FORUM_MEMBERS_PENDING.M_LASTPOSTDATE, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_TITLE, FORUM_MEMBERS_PENDING.M_SUBSCRIPTION, FORUM_MEMBERS_PENDING.M_HIDE_EMAIL, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_RECEIVE_EMAIL, FORUM_MEMBERS_PENDING.M_LAST_IP, FORUM_MEMBERS_PENDING.M_IP, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_FIRSTNAME, FORUM_MEMBERS_PENDING.M_LASTNAME, FORUM_MEMBERS_PENDING.M_OCCUPATION, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_SEX, FORUM_MEMBERS_PENDING.M_AGE, FORUM_MEMBERS_PENDING.M_DOB, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_HOBBIES, FORUM_MEMBERS_PENDING.M_LNEWS, FORUM_MEMBERS_PENDING.M_QUOTE, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_BIO, FORUM_MEMBERS_PENDING.M_MARSTATUS, FORUM_MEMBERS_PENDING.M_LINK1, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_LINK2, FORUM_MEMBERS_PENDING.M_CITY, FORUM_MEMBERS_PENDING.M_STATE, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_PHOTO_URL, FORUM_MEMBERS_PENDING.M_KEY, FORUM_MEMBERS_PENDING.M_NEWEMAIL, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_PWKEY, FORUM_MEMBERS_PENDING.M_APPROVE, FORUM_MEMBERS_PENDING.M_SHA256 "
strSqldel = strSqldel & "FROM FORUM_MEMBERS_PENDING "
strSqldel = strSqldel & " WHERE M_STATUS = " & 0
strSqldel = strSqldel & " AND M_LEVEL = " & -1
my_Conn.Execute (strSqldel),,adCmdText + adExecuteNoRecords
'## Forum_SQL - Delete the Member
strSql = "DELETE FROM " & strMemberTablePrefix & "MEMBERS_PENDING "
strSql = strSql & " WHERE M_STATUS = " & 0
strSql = strSql & " AND M_LEVEL = " & -1
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
Response.Write " <br /><p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strHeaderFontSize & """><b>Members Deleted!</b></font></p>" & vbNewLine & _
" <meta http-equiv=""Refresh"" content=""2; URL=admin_accounts_pending.asp" & strPage & """>" & vbNewLine & _
" <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strHeaderFontSize & """>All pending members have been deleted!</font></p>" & vbNewLine & _
" <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """><a href=""admin_accounts_pending.asp" & strPage & """>Back To Members Pending</font></a></p><br />" & vbNewLine
WriteFooter
Response.End
else
aryID = split(selID, ",")
aryIDdel = split(selID, ",")
for i = 0 to ubound(aryIDdel)
'## Forum_SQL - Add the Member to Member_Deleted table
strSqldel = "INSERT INTO FORUM_MEMBERS_DELETED ( MEMBER_ID, M_STATUS, M_NAME, M_USERNAME, M_PASSWORD, M_EMAIL, M_COUNTRY, "
strSqldel = strSqldel & "M_HOMEPAGE, M_SIG, M_VIEW_SIG, M_SIG_DEFAULT, M_DEFAULT_VIEW, M_LEVEL, M_AIM, M_ICQ, M_MSN, M_YAHOO, M_POSTS, "
strSqldel = strSqldel & "M_DATE, M_LASTHEREDATE, M_LASTPOSTDATE, M_TITLE, M_SUBSCRIPTION, M_HIDE_EMAIL, M_RECEIVE_EMAIL, M_LAST_IP, M_IP, "
strSqldel = strSqldel & "M_FIRSTNAME, M_LASTNAME, M_OCCUPATION, M_SEX, M_AGE, M_DOB, M_HOBBIES, M_LNEWS, M_QUOTE, M_BIO, M_MARSTATUS, "
strSqldel = strSqldel & "M_LINK1, M_LINK2, M_CITY, M_STATE, M_PHOTO_URL, M_KEY, M_NEWEMAIL, M_PWKEY, M_APPROVE, M_SHA256 ) "
strSqldel = strSqldel & "SELECT FORUM_MEMBERS_PENDING.MEMBER_ID, FORUM_MEMBERS_PENDING.M_STATUS, FORUM_MEMBERS_PENDING.M_NAME, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_USERNAME, FORUM_MEMBERS_PENDING.M_PASSWORD, FORUM_MEMBERS_PENDING.M_EMAIL, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_COUNTRY, FORUM_MEMBERS_PENDING.M_HOMEPAGE, FORUM_MEMBERS_PENDING.M_SIG, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_VIEW_SIG, FORUM_MEMBERS_PENDING.M_SIG_DEFAULT, FORUM_MEMBERS_PENDING.M_DEFAULT_VIEW, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_LEVEL, FORUM_MEMBERS_PENDING.M_AIM, FORUM_MEMBERS_PENDING.M_ICQ, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_MSN, FORUM_MEMBERS_PENDING.M_YAHOO, FORUM_MEMBERS_PENDING.M_POSTS, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_DATE, FORUM_MEMBERS_PENDING.M_LASTHEREDATE, FORUM_MEMBERS_PENDING.M_LASTPOSTDATE, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_TITLE, FORUM_MEMBERS_PENDING.M_SUBSCRIPTION, FORUM_MEMBERS_PENDING.M_HIDE_EMAIL, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_RECEIVE_EMAIL, FORUM_MEMBERS_PENDING.M_LAST_IP, FORUM_MEMBERS_PENDING.M_IP, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_FIRSTNAME, FORUM_MEMBERS_PENDING.M_LASTNAME, FORUM_MEMBERS_PENDING.M_OCCUPATION, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_SEX, FORUM_MEMBERS_PENDING.M_AGE, FORUM_MEMBERS_PENDING.M_DOB, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_HOBBIES, FORUM_MEMBERS_PENDING.M_LNEWS, FORUM_MEMBERS_PENDING.M_QUOTE, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_BIO, FORUM_MEMBERS_PENDING.M_MARSTATUS, FORUM_MEMBERS_PENDING.M_LINK1, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_LINK2, FORUM_MEMBERS_PENDING.M_CITY, FORUM_MEMBERS_PENDING.M_STATE, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_PHOTO_URL, FORUM_MEMBERS_PENDING.M_KEY, FORUM_MEMBERS_PENDING.M_NEWEMAIL, "
strSqldel = strSqldel & "FORUM_MEMBERS_PENDING.M_PWKEY, FORUM_MEMBERS_PENDING.M_APPROVE, FORUM_MEMBERS_PENDING.M_SHA256 "
strSqldel = strSqldel & "FROM FORUM_MEMBERS_PENDING "
strSqldel = strSqldel & "WHERE MEMBER_ID = " & aryIDdel(i)
my_Conn.Execute (strSqldel),,adCmdText + adExecuteNoRecords
next
aryID = split(selID, ",")
for i = 0 to ubound(aryID)
'## Forum_SQL - Delete the Member
strSql = "DELETE FROM " & strMemberTablePrefix & "MEMBERS_PENDING "
strSql = strSql & " WHERE MEMBER_ID = " & aryID(i)
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
next
Response.Write " <br /><p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strHeaderFontSize & """><b>Members Deleted!</b></font></p>" & vbNewLine & _
" <meta http-equiv=""Refresh"" content=""2; URL=admin_accounts_pending.asp" & strPage & """>" & vbNewLine & _
" <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strHeaderFontSize & """>Selected members have been deleted!</font></p>" & vbNewLine & _
" <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """><a href=""admin_accounts_pending.asp" & strPage & """>Back To Members Pending</font></a></p><br />" & vbNewLine
WriteFooter
Response.End
end if
end if
Make a backup BEFORE you test or try this modification! |
|
|
| |
Topic |
|
|
|
Topic Locked
