inc_create_forum_sql7.asp

一个不错的ASP论坛源码

	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC70 PRIMARY KEY  NONCLUSTERED "
	strSql = strSql & "(CAT_ID,	FORUM_ID, TOPIC_ID,	REPLY_ID )"
	
	my_Conn.Execute strSql
	ChkDBInstall()

	strSql = "ALTER TABLE " & strTablePrefix & "TOPICS WITH NOCHECK ADD "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC71 DEFAULT 0 FOR CAT_ID, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC72 DEFAULT 0 FOR FORUM_ID, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC73 DEFAULT 1 FOR T_STATUS, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC74 DEFAULT 0 FOR T_MAIL, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC75 DEFAULT 0 FOR T_AUTHOR, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC76 DEFAULT 0 FOR T_REPLIES, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC77 DEFAULT 0 FOR T_VIEW_COUNT, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC78 DEFAULT '' FOR T_LAST_POST, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC79 DEFAULT '' FOR T_DATE, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC80 DEFAULT 0 FOR T_LAST_POSTER, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC81 DEFAULT '000.000.000.000' FOR T_IP, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC82 PRIMARY KEY  NONCLUSTERED "
	strSql = strSql & "(CAT_ID, FORUM_ID, TOPIC_ID )" 

	my_Conn.Execute strSql
	ChkDBInstall()

	strSql = "ALTER TABLE " & strTablePrefix & "TOTALS WITH NOCHECK ADD "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC83 DEFAULT 0 FOR COUNT_ID, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC84 DEFAULT 0 FOR P_COUNT, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC85 DEFAULT 0 FOR T_COUNT, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC86 DEFAULT 0 FOR U_COUNT, "
	strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzC87 PRIMARY KEY  NONCLUSTERED " 
	strSql = strSql & "(COUNT_ID) "

end if

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "CATEGORY_CAT_ID ON " & strTablePrefix & "CATEGORY(CAT_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "CATEGORY_CAT_STATUS ON " & strTablePrefix & "CATEGORY(CAT_STATUS)"

my_Conn.Execute strSql
ChkDBInstall()
	
strSql = "CREATE INDEX " & strTablePrefix & "CAT_ID ON " & strTablePrefix & "FORUM(CAT_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "F_CAT ON " & strTablePrefix & "FORUM(CAT_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "FORUM_ID ON " & strTablePrefix & "FORUM(FORUM_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strMemberTablePrefix & "MEMBERS_MEMBER_ID ON " &strMemberTablePrefix & "MEMBERS(MEMBER_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "MODERATOR_FORUM_ID ON " & strTablePrefix & "MODERATOR(FORUM_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "MODERATORS_MEMBER_ID ON " & strTablePrefix & "MODERATOR(MEMBER_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "REPLY_R_AUTHOR ON " & strTablePrefix & "REPLY(R_AUTHOR)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "REPLY_CATFORTOP_ID ON " & strTablePrefix & "REPLY(CAT_ID, FORUM_ID, TOPIC_ID)"

my_Conn.Execute strSql
ChkDBInstall()


strSql = "CREATE INDEX " & strTablePrefix & "FORUM_ID ON " & strTablePrefix & "REPLY(FORUM_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "REPLY_ID ON " & strTablePrefix & "REPLY(REPLY_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "REPLY_TOPIC_ID ON " & strTablePrefix & "REPLY(TOPIC_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "TOPICS_CAT_ID_FORUM_ID ON " & strTablePrefix & "TOPICS(CAT_ID, FORUM_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "TOPICS_T_AUTHOR ON " & strTablePrefix & "TOPICS(T_AUTHOR)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "TOPICS_CAT_ID ON " & strTablePrefix & "TOPICS(CAT_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "FORUM_ID ON " & strTablePrefix & "TOPICS(FORUM_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "TOPICS_TOPIC_ID ON " & strTablePrefix & "TOPICS(TOPIC_ID)"

my_Conn.Execute strSql
ChkDBInstall()

strSql = "CREATE INDEX " & strTablePrefix & "TOPICS_CAT_FOR_TOP ON " & strTablePrefix & "TOPICS(CAT_ID, FORUM_ID, TOPIC_ID)"

my_Conn.Execute strSql
ChkDBInstall()

' strSql = "ALTER TABLE " & strTablePrefix & "FORUM ADD "
' strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzFK1 FOREIGN KEY "
' strSql = strSql & " (CAT_ID "
' strSql = strSql & " ) REFERENCES " & strTablePrefix & "CATEGORY (CAT_ID) "

' my_Conn.Execute strSql
' ChkDBInstall()

' strSql = "ALTER TABLE " & strTablePrefix & "REPLY ADD "
' strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzFK2 FOREIGN KEY  "
' strSql = strSql & " (R_AUTHOR "
' strSql = strSql & " ) REFERENCES " & strMemberTablePrefix & "MEMBERS (MEMBER_ID), "
' strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzFK3 FOREIGN KEY  "
' strSql = strSql & " (CAT_ID, FORUM_ID, TOPIC_ID "
' strSql = strSql & " ) REFERENCES " & strTablePrefix & "TOPICS (CAT_ID, FORUM_ID, TOPIC_ID) "

' my_Conn.Execute strSql
' ChkDBInstall() 

' strSql = "ALTER TABLE " & strTablePrefix & "TOPICS ADD  "
' strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzFK4 FOREIGN KEY  "
' strSql = strSql & " (CAT_ID, FORUM_ID "
' strSql = strSql & " ) REFERENCES " & strTablePrefix & "FORUM (CAT_ID, FORUM_ID), "
' strSql = strSql & "CONSTRAINT " & strTablePrefix & "SnitzFK5 FOREIGN KEY  "
' strSql = strSql & " (T_AUTHOR "
' strSql = strSql & " ) REFERENCES " & strMemberTablePrefix & "MEMBERS (MEMBER_ID) "

' my_Conn.Execute strSql
' ChkDBInstall()

' strSql = "SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON "

' my_Conn.Execute strSql
' ChkDBInstall()

' strSql = "CREATE TRIGGER " & strTablePrefix & "CATEGORY_UpdateTrigger ON " & strTablePrefix & "CATEGORY FOR UPDATE AS "
' ## CASCADE UPDATES TO FORUM_FORUM
' strSql = strSql & "IF UPDATE(CAT_ID) "
' strSql = strSql & "BEGIN "
' strSql = strSql & "  UPDATE " & strTablePrefix & "FORUM "
' strSql = strSql & "  SET " & strTablePrefix & "FORUM.CAT_ID = inserted.CAT_ID "
' strSql = strSql & "  FROM " & strTablePrefix & "FORUM, deleted, inserted "
' strSql = strSql & "  WHERE deleted.CAT_ID = " & strTablePrefix & "FORUM.CAT_ID "
' strSql = strSql & "END "

' my_Conn.Execute strSql
' ChkDBInstall()

' strSql = "CREATE TRIGGER " & strTablePrefix & "CATEGORY_DeleteTrigger ON " & strTablePrefix & "CATEGORY FOR DELETE AS "
' ## CASCADE DELETES TO FORUM_FORUM
' strSql = strSql & "DELETE " & strTablePrefix & "FORUM FROM deleted, " & strTablePrefix & "FORUM WHERE deleted.CAT_ID = " & strTablePrefix & "FORUM.CAT_ID  "

' my_Conn.Execute strSql
' ChkDBInstall()

' strSql = "CREATE TRIGGER " & strTablePrefix & "FORUM_InsertTrigger ON " & strTablePrefix & "FORUM FOR INSERT AS "
' ## PREVENT INSERTS IF NO MATCHING KEY IN FORUM_CATEGORY
' strSql = strSql & "IF (SELECT COUNT(*) FROM inserted) != "
' strSql = strSql & "(SELECT COUNT(*) FROM " & strTablePrefix & "CATEGORY, inserted WHERE (" & strTablePrefix & "CATEGORY.CAT_ID = inserted.CAT_ID)) "
' strSql = strSql & "BEGIN "
' strSql = strSql & "  RAISERROR 44447 'The record can''t be added or changed. Referential integrity rules require a related record in table ''" & strTablePrefix & "CATEGORY''.' "
' strSql = strSql & "  ROLLBACK TRANSACTION "
' strSql = strSql & "END "

' my_Conn.Execute strSql
' ChkDBInstall()

' strSql = "CREATE TRIGGER " & strTablePrefix & "FORUM_DeleteTrigger ON " & strTablePrefix & "FORUM FOR DELETE AS "
' ## CASCADE DELETES TO FORUM_TOPICS
' strSql = strSql & "DELETE " & strTablePrefix & "TOPICS FROM deleted, " & strTablePrefix & "TOPICS WHERE deleted.CAT_ID = " & strTablePrefix & "TOPICS.CAT_ID AND deleted.FORUM_ID = " & strTablePrefix & "TOPICS.FORUM_ID "

' my_Conn.Execute strSql
' ChkDBInstall()

' strSql = "CREATE TRIGGER " & strTablePrefix & "FORUM_UpdateTrigger ON " & strTablePrefix & "FORUM FOR UPDATE AS "
' ## PREVENT UPDATES IF NO MATCHING KEY IN FORUM_CATEGORY
' strSql = strSql & "IF UPDATE(CAT_ID) "
' strSql = strSql & "BEGIN "
' strSql = strSql & "  IF (SELECT COUNT(*) FROM inserted) != "
' strSql = strSql & "  (SELECT COUNT(*) FROM " & strTablePrefix & "CATEGORY, inserted WHERE (" & strTablePrefix & "CATEGORY.CAT_ID = inserted.CAT_ID)) "
' strSql = strSql & "  BEGIN "
' strSql = strSql & "    RAISERROR 44446 'The record can''t be added or changed. Referential integrity rules require a related record in table ''" & strTablePrefix & "CATEGORY''.' "
' strSql = strSql & "    ROLLBACK TRANSACTION "
' strSql = strSql & "    END "
' strSql = strSql & "END "
' ## CASCADE UPDATES TO FORUM_TOPICS
' strSql = strSql & "IF UPDATE(CAT_ID) OR UPDATE(FORUM_ID) "
' strSql = strSql & "BEGIN "
' strSql = strSql & "  UPDATE " & strTablePrefix & "TOPICS "
' strSql = strSql & "  SET " & strTablePrefix & "TOPICS.CAT_ID = inserted.CAT_ID , " & strTablePrefix & "TOPICS.FORUM_ID = inserted.FORUM_ID "
' strSql = strSql & "  FROM " & strTablePrefix & "TOPICS, deleted, inserted "
' strSql = strSql & "   WHERE deleted.CAT_ID = " & strTablePrefix & "TOPICS.CAT_ID AND deleted.FORUM_ID = " & strTablePrefix & "TOPICS.FORUM_ID "
' strSql = strSql & "END "

' my_Conn.Execute strSql
' ChkDBInstall()

' strSql = "CREATE TRIGGER " & strMemberTablePrefix & "MEMBERS_UpdateTrigger ON " & strMemberTablePrefix & "MEMBERS FOR UPDATE AS "
' ## PREVENT UPDATES IF DEPENDENT RECORDS IN FORUM_REPLY
' strSql = strSql & "IF UPDATE(MEMBER_ID) "
' strSql = strSql & "BEGIN "
' strSql = strSql & "  IF (SELECT COUNT(*) FROM deleted, " & strTablePrefix & "REPLY WHERE (deleted.MEMBER_ID = " & strTablePrefix & "REPLY.R_AUTHOR)) > 0 "
' strSql = strSql & "  BEGIN "
' strSql = strSql & "    RAISERROR 44446 'The record can''t be deleted or changed. Since related records exist in table ''" & strTablePrefix & "REPLY'', referential integrity rules would be violated.' "
' strSql = strSql & "    ROLLBACK TRANSACTION "
' strSql = strSql & "  END "
' strSql = strSql & "END "
' ## PREVENT UPDATES IF DEPENDENT RECORDS IN FORUM_TOPICS
' strSql = strSql & "IF UPDATE(MEMBER_ID) "
' strSql = strSql & "BEGIN "
' strSql = strSql & "  IF (SELECT COUNT(*) FROM deleted, " & strTablePrefix & "TOPICS WHERE (deleted.MEMBER_ID = " & strTablePrefix & "TOPICS.T_AUTHOR)) > 0 "