March 22, 2007 at 1:15 pm
Here is the error message that I'm getting. Everything else seems to work all right. I don't know what is wrong. Please help.
Server: Msg 15135, Level 16, State 1, Procedure sp_validatepropertyinputs, Line 255 Object is invalid. Extended properties are not permitted on 'dbo.Categories.TestID', or the object does not exist.
Here is my code.
/*
----------------------------------------------------------------------------------------
Created by: Jacob Pressures
Date: 07/19/2005
Total Max Size: 102GB
Total No. of Tables: 17
Version: 1.0
Mod Date: 03/19/2007
----------------------------------------------------------------------------------------
TABLE OF CONTENTS
1.0 FSS1 Database & File Creation
2.0 FSS1 Tables
3.0 Data Dictionary
4.0 Triggers
----------------------------------------------------------------------------------------
*/
----------------------------------------------------------------------------------------
-- 1.0 FSS1 Database & File Creation
----------------------------------------------------------------------------------------
Use master
GO
CREATE DATABASE FSS1
ON PRIMARY
(
NAME = FSS1_Data1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\FSS1_Data1.mdf',
MAXSIZE = 50GB,
SIZE = 20MB,
FILEGROWTH = 10%
),
(
NAME = FSS1_Data2,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\FSS1_Data2.ndf',
MAXSIZE = 50GB,
SIZE = 20MB,
FILEGROWTH = 10%
)
LOG ON
(
NAME = FSS1_log1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\FSS1_log1.ldf',
MAXSIZE = 1GB,
SIZE = 10MB,
FILEGROWTH = 10%
),
(
NAME = FSS1_log2,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\FSS1_log2.ldf',
MAXSIZE = 1GB,
SIZE = 10MB,
FILEGROWTH = 10%
)
GO
----------------------------------------------------------------------------------------
-- 2.0 FSS1 Tables
----------------------------------------------------------------------------------------
Use FSS1
GO
CREATE TABLE FSS1.dbo.Centers
(
CenterID tinyint NOT NULL IDENTITY PRIMARY KEY,
Center varchar(35) NOT NULL,
Inactive bit NOT NULL CONSTRAINT df_CentersInactive DEFAULT 0
)
CREATE TABLE FSS1.dbo.Departments
(
DeptID tinyint NOT NULL IDENTITY PRIMARY KEY,
Department varchar(35) NOT NULL,
Inactive bit NOT NULL CONSTRAINT df_DepartmentsInactive DEFAULT 0
)
CREATE TABLE FSS1.dbo.Employees
(
EmpID smallint NOT NULL IDENTITY PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL,
BadgeNumber char(5) NOT NULL,
DeptID tinyint NOT NULL FOREIGN KEY REFERENCES dbo.Departments,
CenterID tinyint NOT NULL FOREIGN KEY REFERENCES dbo.Centers,
Inactive bit NOT NULL CONSTRAINT df_EmployeesInactive DEFAULT 0
)
CREATE TABLE FSS1.dbo.Supervisors
(
SupervisorID tinyint NOT NULL IDENTITY PRIMARY KEY,
EmpID smallint NOT NULL FOREIGN KEY REFERENCES dbo.Employees,
Description varchar(100) NOT NULL,
Password varchar(20) NOT NULL,
DateBegan smalldatetime NOT NULL,
DateEnded smalldatetime NULL,
Inactive bit NOT NULL CONSTRAINT df_SupervisorsInactive DEFAULT 0
)
CREATE TABLE FSS1.dbo.ResearchTypes
(
ResearchTypeID tinyint NOT NULL IDENTITY PRIMARY KEY,
ResearchType varchar(25) NOT NULL,
Inactive bit NOT NULL CONSTRAINT df_ResearchTypesInactive Default 0
)
CREATE TABLE FSS1.dbo.ResearchTopics
(
ResearchTopicID tinyint NOT NULL IDENTITY PRIMARY KEY,
ResearchTypeID tinyint NOT NULL FOREIGN KEY REFERENCES dbo.ResearchTypes,
ResearchTopic varchar(50) NOT NULL,
Inactive bit NOT NULL CONSTRAINT df_ResearchTopicsInactive Default 0
)
CREATE TABLE FSS1.dbo.Categories
(
CategoryID smallint NOT NULL IDENTITY PRIMARY KEY,
ResearchTopicID tinyint NOT NULL FOREIGN KEY REFERENCES dbo.ResearchTopics,
ResearchTypeID tinyint NOT NULL FOREIGN KEY REFERENCES dbo.ResearchTypes,
Category varchar(50) NOT NULL,
CategoryPurpose varchar(250) NULL,
SelectionNumber tinyint NOT NULL CONSTRAINT df_CategoriesSelectionNumber
DEFAULT 6,
RevisedDate smalldatetime NOT NULL CONSTRAINT df_CategoriesRevDate DEFAULT
getdate(),
Inactive bit NOT NULL CONSTRAINT df_CategoriesInactive DEFAULT 0
)
CREATE TABLE FSS1.dbo.Learning
(
LearningID smallint NOT NULL IDENTITY PRIMARY KEY,
CategoryID smallint NOT NULL FOREIGN KEY REFERENCES dbo.Categories,
LearningObjective varchar(200) NOT NULL,
RevisedDate smalldatetime NOT NULL CONSTRAINT df_LearningRevDate DEFAULT
getdate(),
Inactive bit NOT NULL CONSTRAINT df_LearningInactive DEFAULT 0
)
CREATE TABLE FSS1.dbo.Questions
(
QID smallint NOT NULL IDENTITY PRIMARY KEY,
CategoryID smallint NOT NULL FOREIGN KEY REFERENCES dbo.Categories,
Question varchar(450) NOT NULL,
Reference varchar(150) NOT NULL,
RevisedDate smalldatetime NULL CONSTRAINT df_QuestionsRevDate DEFAULT
getdate(),
Inactive bit NOT NULL CONSTRAINT df_QuestionsInactive DEFAULT 0
)
ALTER TABLE FSS1.dbo.Questions
ADD CONSTRAINT df_NoReference DEFAULT 'Not Available' FOR Reference
CREATE TABLE FSS1.dbo.Resources
(
ResourceID smallint NOT NULL IDENTITY PRIMARY KEY,
ResourceTitle varchar(50) NOT NULL,
FileName varchar(200) NOT NULL,
FileType char(4) NOT NULL,
DisallowCategories bit NOT NULL CONSTRAINT df_ResourcesDisallowCategories
DEFAULT 1,
DisallowQuestions bit NOT NULL CONSTRAINT df_ResourcesDisallowQuestions
DEFAULT 1,
Explanation varchar(3000) NULL,
RevisedDate smalldatetime NOT NULL CONSTRAINT df_ResourcesRevDate DEFAULT
getdate(),
Inactive bit NOT NULL CONSTRAINT df_ResourcesInactive DEFAULT 0
)
CREATE TABLE FSS1.dbo.CategoryResources
(
CRID smallint NOT NULL IDENTITY PRIMARY KEY,
CategoryID smallint NOT NULL FOREIGN KEY REFERENCES dbo.Categories,
ResourceID smallint NOT NULL FOREIGN KEY REFERENCES dbo.Resources,
RevisedDate smalldatetime NOT NULL CONSTRAINT df_CategoryResourcesRevDate DEFAULT
getdate(),
Inactive bit NOT NULL CONSTRAINT df_CategoryResourcesInactive DEFAULT 0
)
CREATE TABLE FSS1.dbo.QuestionResources
(
QRID smallint NOT NULL IDENTITY PRIMARY KEY,
QID smallint NOT NULL FOREIGN KEY REFERENCES dbo.Questions,
ResourceID smallint NOT NULL FOREIGN KEY REFERENCES dbo.Resources,
RevisedDate smalldatetime NOT NULL CONSTRAINT df_QuestionResourcesRevDate DEFAULT
getdate(),
Inactive bit NOT NULL CONSTRAINT df_QuestionResourcesInactive DEFAULT 0
)
CREATE TABLE FSS1.dbo.Answers
(
AnswerID smallint NOT NULL IDENTITY PRIMARY KEY,
QID smallint NOT NULL FOREIGN KEY REFERENCES dbo.Questions,
Answer varchar(300) NOT NULL,
Correct bit NOT NULL,
RevisedDate smalldatetime NOT NULL CONSTRAINT df_AnswersRevDate DEFAULT getdate()
)
CREATE TABLE FSS1.dbo.Completed_Topics
(
CTID int NOT NULL IDENTITY PRIMARY KEY,
ResearchTopicID tinyint NOT NULL FOREIGN KEY REFERENCES dbo.ResearchTopics,
EmpID smallint NOT NULL FOREIGN KEY REFERENCES dbo.Employees,
SupervisorID tinyint NULL FOREIGN KEY REFERENCES dbo.Supervisors,
CTStartDate smalldatetime NULL,
CTFinishDate smalldatetime NULL
)
CREATE TABLE FSS1.dbo.Completed_Categories
(
CCID int NOT NULL IDENTITY PRIMARY KEY,
CTID int NOT NULL FOREIGN KEY REFERENCES dbo.Completed_Topics,
CategoryID smallint NOT NULL FOREIGN KEY REFERENCES dbo.Categories,
StartTime smalldatetime NULL,
FinishTIme smalldatetime NULL,
Comment varchar(3000) NULL
)
CREATE TABLE FSS1.dbo.Completed_Questions
(
CQID int NOT NULL IDENTITY PRIMARY KEY,
QID smallint NOT NULL FOREIGN KEY REFERENCES dbo.Questions,
CCID int NOT NULL FOREIGN KEY REFERENCES dbo.Completed_Categories,
Comment varchar(3000) NULL
)
CREATE TABLE FSS1.dbo.Responses
(
ResponseID int NOT NULL IDENTITY PRIMARY KEY,
CQID int NOT NULL FOREIGN KEY REFERENCES dbo.Completed_Questions,
AnswerID smallint NULL FOREIGN KEY REFERENCES dbo.Answers
)
GO
----------------------------------------------------------------------------------------
-- 3.0 Data Dictionary
----------------------------------------------------------------------------------------
Use FSS1
GO
-- CENTERS TABLE -----------------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'Centers', 'column', 'CenterID'
EXEC sp_addextendedproperty
'MS_Description', 'Name of each Franklin center', 'User', 'dbo', 'table', 'Centers', 'column', 'Center'
EXEC sp_addextendedproperty
'MS_Description', 'Boolean value identifying center as active or inactive', 'User', 'dbo', 'table', 'Centers', 'column', 'Inactive'
GO
-- DEPARTMENTS TABLE -------------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'Departments', 'column', 'DeptID'
EXEC sp_addextendedproperty
'MS_Description', 'Name of each Franklin department', 'User', 'dbo', 'table', 'Departments', 'column', 'Department'
EXEC sp_addextendedproperty
'MS_Description', 'Boolean value identifying department as active or inactive', 'User', 'dbo', 'table', 'Departments', 'column', 'Inactive'
GO
-- EMPLOYEES TABLE ---------------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'Employees', 'column', 'EmpID'
EXEC sp_addextendedproperty
'MS_Description', 'Employee''s first name', 'User', 'dbo', 'table', 'Employees', 'column', 'FirstName'
EXEC sp_addextendedproperty
'MS_Description', 'Employee''s last name', 'User', 'dbo', 'table', 'Employees', 'column', 'LastName'
EXEC sp_addextendedproperty
'MS_Description', 'Employee''s badge number or number on check stub for salaried employees without badge numbers', 'User', 'dbo', 'table', 'Employees', 'column', 'BadgeNumber'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Departments table', 'User', 'dbo', 'table', 'Employees', 'column', 'DeptID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Centers table', 'User', 'dbo', 'table', 'Employees', 'column', 'CenterID'
EXEC sp_addextendedproperty
'MS_Description', 'Boolean value identifying employee as active or inactive', 'User', 'dbo', 'table', 'Employees', 'column', 'Inactive'
GO
-- SUPERVISORS TABLE -------------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'Supervisors', 'column', 'SupervisorID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Employees table', 'User', 'dbo', 'table', 'Supervisors', 'column', 'EmpID'
EXEC sp_addextendedproperty
'MS_Description', 'Description of the supervisor''s position', 'User', 'dbo', 'table', 'Supervisors', 'column', 'Description'
EXEC sp_addextendedproperty
'MS_Description', 'Supervisor''s password used for special supervisory access', 'User', 'dbo', 'table', 'Supervisors', 'column', 'Password'
EXEC sp_addextendedproperty
'MS_Description', 'Date supervisor gained supervisory position', 'User', 'dbo', 'table', 'Supervisors', 'column', 'DateBegan'
EXEC sp_addextendedproperty
'MS_Description', 'Date employee left or loss supervisory position', 'User', 'dbo', 'table', 'Supervisors', 'column', 'DateEnded'
EXEC sp_addextendedproperty
'MS_Description', 'Boolean value identifying supervisor as active or inactive', 'User', 'dbo', 'table', 'Supervisors', 'column', 'Inactive'
GO
-- RESEARCHTYPES TABLE -----------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'ResearchTypes', 'column', 'ResearchTypeID'
EXEC sp_addextendedproperty
'MS_Description', 'Description of the type of quiz, test, evaluation, survey, etc found in the ResearchTopics table', 'User', 'dbo', 'table', 'ResearchTypes', 'column', 'ResearchType'
EXEC sp_addextendedproperty
'MS_Description', 'Boolean value identifying research type as active or inactive', 'User', 'dbo', 'table', 'ResearchTypes', 'column', 'Inactive'
GO
-- RESEARCHTOPICS TABLE ----------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'ResearchTopics', 'column', 'ResearchTopicID'
EXEC sp_addextendedproperty
'MS_Description', 'Name of topic', 'User', 'dbo', 'table', 'ResearchTopics', 'column', 'ResearchTopic'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from ResearchTypes table', 'User', 'dbo', 'table', 'ResearchTopics', 'column', 'ResearchTypeID'
EXEC sp_addextendedproperty
'MS_Description', 'Boolean value identifying research topic as active or inactive', 'User', 'dbo', 'table', 'ResearchTopics', 'column', 'Inactive'
GO
-- CATEGORIES TABLE --------------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'Categories', 'column', 'CategoryID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from ResearchTypes table', 'User', 'dbo', 'table', 'Categories', 'column', 'ResearchTypeID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Tests table', 'User', 'dbo', 'table', 'Categories', 'column', 'TestID'
EXEC sp_addextendedproperty
'MS_Description', 'Category''s name', 'User', 'dbo', 'table', 'Categories', 'column', 'Category'
EXEC sp_addextendedproperty
'MS_Description', 'Description of the category’s purpose', 'User', 'dbo', 'table', 'Categories', 'column', 'CategoryPurpose'
EXEC sp_addextendedproperty
'MS_Description', 'Number of required questions to select from each category', 'User', 'dbo', 'table', 'Categories', 'column', 'SelectionNumber'
EXEC sp_addextendedproperty
'MS_Description', 'Boolean value identifying category as active or inactive', 'User', 'dbo', 'table', 'Categories', 'column', 'Inactive'
GO
-- LEARNING TABLE ----------------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'Learning', 'column', 'LearningID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Categories table', 'User', 'dbo', 'table', 'Learning', 'column', 'CategoryID'
EXEC sp_addextendedproperty
'MS_Description', 'Learning objective', 'User', 'dbo', 'table', 'Learning', 'column', 'LearningObjective'
EXEC sp_addextendedproperty
'MS_Description', 'Last time objective was revised', 'User', 'dbo', 'table', 'Learning', 'column', 'RevisedDate'
EXEC sp_addextendedproperty
'MS_Description', 'Boolean value identifying learning objective as active or inactive', 'User', 'dbo', 'table', 'Learning', 'column', 'Inactive'
GO
-- QUESTIONS TABLE ---------------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'Questions', 'column', 'QID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Categories table; identifies which questions belong to which category.', 'User', 'dbo', 'table', 'Questions', 'column', 'CategoryID'
EXEC sp_addextendedproperty
'MS_Description', 'Exam question', 'User', 'dbo', 'table', 'Questions', 'column', 'Question'
EXEC sp_addextendedproperty
'MS_Description', 'Manuals and page numbers', 'User', 'dbo', 'table', 'Questions', 'column', 'Reference'
EXEC sp_addextendedproperty
'MS_Description', 'Last time question and related info were revised', 'User', 'dbo', 'table', 'Questions', 'column', 'RevisedDate'
EXEC sp_addextendedproperty
'MS_Description', 'Boolean value identifying question as active or inactive', 'User', 'dbo', 'table', 'Questions', 'column', 'Inactive'
GO
-- RESOURCES TABLE ---------------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'Resources', 'column', 'ResourceID'
EXEC sp_addextendedproperty
'MS_Description', 'Official title of the file, video, manual, etc.', 'User', 'dbo', 'table', 'Resources', 'column', 'ResourceTitle'
EXEC sp_addextendedproperty
'MS_Description', 'Address or file name and extension', 'User', 'dbo', 'table', 'Resources', 'column', 'FileName'
EXEC sp_addextendedproperty
'MS_Description', 'File type (such as PDF, AVI, MP3)', 'User', 'dbo', 'table', 'Resources', 'column', 'FileType'
EXEC sp_addextendedproperty
'MS_Description', 'Date material was last updated', 'User', 'dbo', 'table', 'Resources', 'column', 'RevisedDate'
EXEC sp_addextendedproperty
'MS_Description', 'Determines whether the resource can be used on the category level', 'User', 'dbo', 'table', 'Resources', 'column', 'DisallowCategories'
EXEC sp_addextendedproperty
'MS_Description', 'Determines whether the resource can be used on the question level', 'User', 'dbo', 'table', 'Resources', 'column', 'DisallowQuestions'
EXEC sp_addextendedproperty
'MS_Description', 'Statement explaining why a resource is disallowed on either category or question levels', 'User', 'dbo', 'table', 'Resources', 'column', 'Explanation'
EXEC sp_addextendedproperty
'MS_Description', 'Boolean value identifying resource as active or inactive', 'User', 'dbo', 'table', 'Resources', 'column', 'Inactive'
GO
-- CATEGORYRESOURCES TABLE -------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'CategoryResources', 'column', 'CRID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Categories table', 'User', 'dbo', 'table', 'CategoryResources', 'column', 'CategoryID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Resources table', 'User', 'dbo', 'table', 'CategoryResources', 'column', 'ResourceID'
EXEC sp_addextendedproperty
'MS_Description', 'Boolean value identifying resource as active or inactive', 'User', 'dbo', 'table', 'CategoryResources', 'column', 'Inactive'
GO
-- QUESTIONRESOURCES TABLE -------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'QuestionResources', 'column', 'QRID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Questions table', 'User', 'dbo', 'table', 'QuestionResources', 'column', 'QID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Resources table', 'User', 'dbo', 'table', 'QuestionResources', 'column', 'ResourceID'
EXEC sp_addextendedproperty
'MS_Description', 'Boolean value identifying resource as active or inactive', 'User', 'dbo', 'table', 'QuestionResources', 'column', 'Inactive'
GO
-- ANSWERS TABLE -----------------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'Answers', 'column', 'AnswerID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Questions table', 'User', 'dbo', 'table', 'Answers', 'column', 'QID'
EXEC sp_addextendedproperty
'MS_Description', 'Possible answer to a particular question', 'User', 'dbo', 'table', 'Answers', 'column', 'Answer'
EXEC sp_addextendedproperty
'MS_Description', 'Boolean value describing whether answer is a correct choice or not.', 'User', 'dbo', 'table', 'Answers', 'column', 'Correct'
EXEC sp_addextendedproperty
'MS_Description', 'Date answer was last revised', 'User', 'dbo', 'table', 'Answers', 'column', 'RevisedDate'
GO
-- COMPLETED_TOPICS TABLE --------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'Completed_Topics', 'column', 'CTID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Tests table', 'User', 'dbo', 'table', 'Completed_Topics', 'column', 'ResearchTopicID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Employees table', 'User', 'dbo', 'table', 'Completed_Topics', 'column', 'EmpID'
EXEC sp_addextendedproperty
'MS_Description', 'Date employee test began', 'User', 'dbo', 'table', 'Completed_Topics', 'column', 'CTStartDate'
EXEC sp_addextendedproperty
'MS_Description', 'Date employee test was completed', 'User', 'dbo', 'table', 'Completed_Topics', 'column', 'CTFinishDate'
GO
-- COMPLETED_CATEGORIES TABLE ----------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'Completed_Categories', 'column', 'CCID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Completed_Topics table; used to identify which completed categories belong to each employee test.', 'User', 'dbo', 'table', 'Completed_Categories', 'column', 'CTID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Categories table; used to identify tested or completed category', 'User', 'dbo', 'table', 'Completed_Categories', 'column', 'CategoryID'
EXEC sp_addextendedproperty
'MS_Description', 'Stores the date and time testing of objective began', 'User', 'dbo', 'table', 'Completed_Categories', 'column', 'StartTime'
EXEC sp_addextendedproperty
'MS_Description', 'Stores the date and time testing of objective was completed.', 'User', 'dbo', 'table', 'Completed_Categories', 'column', 'FinishTime'
EXEC sp_addextendedproperty
'MS_Description', 'Additional comments made by computer or other user regarding the status of a particular completed category.', 'User', 'dbo', 'table', 'Completed_Categories', 'column', 'Comment'
GO
-- COMPLETED_QUESTIONS TABLE ----------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'Completed_Questions', 'column', 'CQID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Questions table.', 'User', 'dbo', 'table', 'Completed_Questions', 'column', 'QID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Completed_Categories table.', 'User', 'dbo', 'table', 'Completed_Questions', 'column', 'CCID'
EXEC sp_addextendedproperty
'MS_Description', 'Employee''s comments for questions that allow this option.', 'User', 'dbo', 'table', 'Completed_Questions', 'column', 'Comment'
GO
-- RESPONSES TABLE ---------------------------------------------------------------------
EXEC sp_addextendedproperty
'MS_Description', 'Primary key', 'User', 'dbo', 'table', 'Responses', 'column', 'ResponseID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Completed_Questions table', 'User', 'dbo', 'table', 'Responses', 'column', 'CQID'
EXEC sp_addextendedproperty
'MS_Description', 'Foreign key from Answers table.', 'User', 'dbo', 'table', 'Responses', 'column', 'AnswerID'
GO
----------------------------------------------------------------------------------------
-- 4.0 Triggers
----------------------------------------------------------------------------------------
Use FSS1
GO
-- RESOURCES TABLE ---------------------------------------------------------------------
CREATE TRIGGER trResources ON Resources
AFTER Update
AS
SET NoCount ON
UPDATE r
SET r.RevisedDate = getDate()
FROM Resources r
JOIN Inserted i
ON i.ResourceID = r.ResourceID
GO
-- LEARNING TABLE ----------------------------------------------------------------------
CREATE TRIGGER trLearning ON Learning
AFTER Update
AS
SET NoCount ON
UPDATE l
SET l.RevisedDate = getDate()
FROM Learning l
JOIN Inserted i
ON i.LearningID = l.LearningID
GO
-- QUESTIONS TABLE ---------------------------------------------------------------------
CREATE TRIGGER trQuestions ON Questions
AFTER Update
AS
SET NoCount ON
UPDATE q
SET q.RevisedDate = getDate()
FROM Questions q
JOIN Inserted i
ON i.QID = q.QID
GO
March 22, 2007 at 1:35 pm
The columns TestID does not exist in the Catagories table.
March 22, 2007 at 1:46 pm
Oh, so that is the problem! Sorry. THanks! I changed the names of the tables months ago and haven't looked a the new code since. I correct a lot of other errors but not this one.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply