November 20, 2020 at 3:40 pm
I am trying to insert some sample data into my database. I ran the INSERT INTO Statements and got the following messages:
/*
*****************************
* Data Entry Statements for *
* Project ACES *
* Version 1.1.0 *
* Written By: Russell Wright*
* Updated by: Russell Wright*
* Created: 11/20/2020 *
* Updated: 11/20/2020 *
*****************************
*/
--Insert Data Into Achievements Table
INSERT INTO Achievements (Achievement, Team_ID)
VALUES
('Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut id nulla id eros sagittis ornare ut at mi. Proin libero enim, luctus ut justo et, commodo posuere leo. Nunc sollicitudin lorem urna, non finibus tellus mattis commodo. Nullam pretium, eros sit amet blandit vulputate, massa nunc vestibulum libero, a vehicula.', 100),
('Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse orci eros, condimentum dignissim dignissim ut, dignissim eu ipsum. In eu scelerisque nibh. In hac habitasse platea dictumst. Aenean aliquet mollis ex, ullamcorper mattis sapien fermentum vitae. Nulla facilisi. Quisque eros lectus, dapibus eget aliquam et, vulputate nec tellus. Etiam tempor.', 101),
('Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed commodo luctus mauris, id commodo odio. Sed tempus et tortor ac cursus. Duis eget arcu vestibulum ligula fermentum ultrices. Mauris id scelerisque metus. Etiam vel consequat urna, eget fermentum arcu. Sed non vestibulum nunc, non ultricies ipsum. Nam sed mauris suscipit.', 102),
('Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse ac mauris id erat scelerisque accumsan ut sit amet justo. Cras condimentum tortor lorem, cursus congue nibh consequat sagittis. Sed et mi sit amet augue iaculis sodales ut nec turpis. Mauris sagittis vel lectus ac tempor. Morbi porttitor blandit arcu, finibus.', 103),
('Lorem ipsum dolor sit amet, consectetur adipiscing elit. Praesent sodales ultrices consectetur. Sed sapien libero, fermentum id accumsan pulvinar, blandit a neque. Phasellus metus lacus, rhoncus suscipit nisl a, scelerisque laoreet orci. Integer facilisis elit non mattis sodales. Sed ligula lectus, pretium sed bibendum eu, sodales accumsan dolor. Aenean non.', 104);
--Insert Data Into Messages Table
INSERT INTO Messages (M_Name, M_Email, M_Phone, M_Message)
VALUES
('Russell Wright', 'russdwright@email', '704-963-0518', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Mauris tincidunt ultrices tellus a suscipit. Etiam luctus mollis elit et tempor. Phasellus non felis sollicitudin, placerat enim in, lacinia velit. Quisque consequat bibendum nibh ac maximus. Nam et accumsan nulla, nec finibus erat. Nunc vitae egestas dolor. Aliquam tristique sodales quam.'),
('Peg Livingston', 'xoticat@email', '918-843-9999', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vestibulum venenatis, odio a consectetur bibendum, purus nunc vestibulum quam, vel fermentum ipsum diam sit amet urna. Cras tempor consequat vulputate. Praesent ut volutpat magna. Ut commodo turpis eu est dapibus consequat nec consectetur nunc. Praesent pulvinar augue justo, vitae varius nibh.'),
('Jeff Kilpatrick', 'jkilpat@email', '317-596-9876', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi tempus eget leo ac tincidunt. Nunc quis tristique diam, sed fermentum mauris. Cras a imperdiet ipsum, nec tincidunt justo. Phasellus scelerisque et tortor sit amet tristique. Vivamus vel efficitur tortor. Sed vel ex in lectus semper sodales. Maecenas aliquam, turpis eu.'),
('Liz Kilpatrick', 'lizkilpatrick@email', '317-987-1234', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis ultrices quam eu lorem consectetur, ut luctus justo tempus. Vivamus vehicula tincidunt turpis, quis congue eros mollis quis. Pellentesque ac lorem varius, aliquet leo vitae, ornare urna. Ut in lobortis ante. Donec auctor varius diam nec consectetur. Praesent a scelerisque augue.'),
('Pam Wright', 'pamk10545@email', '918-698-1353', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed volutpat purus at semper consequat. Nunc cursus tellus eu dui vehicula, vitae ultrices elit tristique. Integer hendrerit diam sit amet pellentesque porttitor. Aliquam scelerisque tristique risus, et semper odio molestie ut. Donec mi sapien, pretium a tempor id, pharetra hendrerit mi.');
--The lines below appear when I run the statements, but performing a SELECT Statement on any table except Achievements brings back data
Msg 545, Level 16, State 1, Line 60
Explicit value must be specified for identity column in table 'Achievements' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
Every table is showing data except for the Achievements table. Below is the code for that table:
--Create Table Achievements
CREATE TABLE Achievements (
Achieve_ID int IDENTITY(100,1),
Achievement varchar(MAX) NOT NULL,
Team_ID int NOT NULL,
CONSTRAINT PK_Achieve_ID
PRIMARY KEY NONCLUSTERED (Achieve_ID),
CONSTRAINT FK_Achieve_Team_ID FOREIGN KEY (Team_ID)
REFERENCES Teams (Team_ID)
);
Is there something I'm missing?
November 20, 2020 at 4:50 pm
Can you please post the full DDL for all the tables, including all keys and constraints?
😎
November 20, 2020 at 9:39 pm
Somehow I was able to get this to work. I may have some other issues, though RE: my triggers. I will provide all the code as well as the errors later this evening, as I'm about to leave to take care of something offline.
November 21, 2020 at 2:31 pm
I have a question based on the error... do you have Replication running or did you have SET IDENTITY _INSERT ON turned on for a table? It's not easy to figure that out from the code you posted.
If the latter, then the answer is Yes, you can only have SET IDENTITY_INSERT ON for one table in a database at a time.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2020 at 2:59 pm
Yeah, I discovered that myself. Again, somehow I was able to make it work.
My new issue as that I have Triggers set up and I have certain columns configured as int, but want to be able to enter 'DELETED' as a value when deletions occur. I know I need to use CAST or CONVERT, but I'm not sure where to place it in relation to the rest of the Trigger. See below:
/*Create Trigger to update Achievements_Audit when record is Deleted*/
CREATE TRIGGER Tr_AchievementsAudit_Delete
ON Achievements
AFTER Delete
AS
BEGIN
DECLARE @Achieve_ID int
DECLARE @AA_AchieveOld varchar(MAX)
DECLARE @AA_TeamID_Old int
SELECT * INTO #AA_Temp FROM Deleted
WHILE (EXISTS (SELECT Achieve_ID FROM #AA_Temp))
BEGIN
SELECT TOP 1
@Achieve_ID = Achieve_ID,
@AA_AchieveOld = Achievement,
@AA_TeamID_Old = Team_ID
FROM #AA_Temp
INSERT INTO Achievements_Audit
(Achieve_ID, AA_AchieveOld, AA_AchieveNew, AA_TeamID_Old, AA_TeamID_New,
AA_ModifiedBy, AA_ModifyDate)
/*Do I place the CAST or CONVERT within the values like I have here, or do I do it prior to this? I
essentially need to convert the column, which is normally int, into varchar for just this purpose; int
values go into this column when inserting or updating records.*/
VALUES
(@Achieve_ID, @AA_AchieveOld, 'DELETED', @AA_TeamID_Old,
CONVERT(varchar(10), 'DELETED'), 'Somewhere', GetDate())
/*I tried this and got a Syntax Error on the comma after the CONVERT*/
DELETE FROM #AA_Temp
WHERE Achieve_ID = @Achieve_ID
END
END
GO
November 21, 2020 at 4:59 pm
Lordy... why are you using a homegrown temp table version of a cursor to do this? It requires you to materialize the DELETED logical table, has two forms of RBAR built into it (the Delete from the temp table is particularly painful and because of a total lack of indexing, basically forms a Triangular Join (half a Cartesian Product or CROSS JOIN).
Get rid of all that stuff and change your INSERT to the audit table to use the DELETED table as the source instead of values. I'd help you rewrite it except that I'd need to see the CREATE TABLE statement for both the Achievements and the audit table.
The other thing I'm looking at is the use of VARCHAR(MAX) for the Achievement column. That seems like serious overkill for a single Achievement name. Of course, it also looks like you're storing achievements as comma separated values and, if so, that will cause you extremely grave pain in the very near future. You need to normalize the data, instead. That means a list of possible achievements in a separate table and then use the identity value of those achievements in separate rows for each achievement per team id.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2020 at 1:12 am
OK, let me start by answering your questions/concerns:
--Create Table Users
CREATE TABLE Users (
User_ID int IDENTITY(100,1), --Tracking number
U_Email varchar(255) NOT NULL, --Primary Key
U_FName varchar(50) NOT NULL,
U_LName varchar(50) NOT NULL,
U_DOB date NOT NULL,
U_Password varchar(255) NOT NULL, /*--Don't get after me about this ....*/
U_CreateDate datetime DEFAULT GetDate();
CONSTRAINT PK_Login
PRIMARY KEY NONCLUSTERED (User_ID, U_Email)
);
--Create USERS_AUDIT Table
CREATE TABLE Users_Audit
(
UA_ID int IDENTITY(100,1),
User_ID int NOT NULL,
UA_EmailOld varchar(255), --Old Email
UA_EmailNew varchar(255), --New Email
UA_FNameOld varchar(100), --Old First Name
UA_FNameNew varchar(100), --New First Name
UA_LNameOld varchar(100), --Old Last Name
UA_LNameNew varchar(100), --New Last Name
UA_DOB_Old date,--Old DOB
UA_DOB_New date,--New DOB
UA_PW_Old varchar(255),--Old PW
UA_PW_New varchar(255),--New PW
UA_ModifiedBy varchar(255),
UA_ModifyDate datetime,
CONSTRAINT PK_Login_Audit
PRIMARY KEY NONCLUSTERED (UA_ID));
--Create Table Messages
CREATE TABLE Messages (
Message_ID int IDENTITY(100,1),
M_Name varchar(100) NOT NULL,
M_Email varchar(100) NOT NULL,
M_Phone varchar(25),
M_Message varchar(MAX),
CONSTRAINT PK_Message
PRIMARY KEY NONCLUSTERED (Message_ID)
);
--Create MESSAGES_AUDIT Table
CREATE TABLE Messages_Audit
(
MA_ID int IDENTITY(100,1),
Message_ID int NOT NULL,
MA_NameOld varchar(100),--Old Name
MA_NameNew varchar(100),--New Name
MA_EmailOld varchar(100),--Old Email
MA_EmailNew varchar(100),--New Email
MA_PhoneOld varchar(25),--Old Phone #
MA_PhoneNew varchar(25),--New Phone #
MA_MessageOld varchar(MAX),--Old Message
MA_MessageNew varchar(MAX),--New Message
MA_ModifiedBy varchar(255),
MA_ModifyDate datetime,
CONSTRAINT PK_Messages_Audit
PRIMARY KEY NONCLUSTERED (MA_ID));
--Create Semesters Table
CREATE TABLE Semesters (
Semester_ID int IDENTITY (100,1), --Primary Key
Sem_Year int,--Year of Semester (e.g., 2020)
Sem_Term varchar(10), --Term (e.g., Fall, Spring, Summer)
Sem_CreateDate date DEFAULT GetDate()--Date entry was created
CONSTRAINT PK_Semester_ID
PRIMARY KEY NONCLUSTERED (Semester_ID)
);
--Create SEMESTERS_AUDIT Table
CREATE TABLE Semesters_Audit (
SemAud_ID int IDENTITY (100,1),
Semester_ID int NOT NULL,
SemYear_Old int,--Old Semester Year
SemYear_New int,--New Semester Year
SemTerm_Old varchar(10),--Old Semester Term
SemTerm_New varchar(10),--New Semester Term
SemAud_ModifiedBy varchar(255),
SemAud_ModifyDate datetime,
CONSTRAINT PK_Semesters_Audit
PRIMARY KEY NONCLUSTERED (SemAud_ID));
--Create Table Teams
CREATE TABLE Teams (
Team_ID int IDENTITY(100,1),
Team_Name varchar(100) NOT NULL,
Team_Project varchar(100) NOT NULL,
Semester_ID int NOT NULL
CONSTRAINT PK_Team_ID
PRIMARY KEY NONCLUSTERED (Team_ID),
CONSTRAINT Teams_Semester_ID FOREIGN KEY (Semester_ID)
REFERENCES Semesters (Semester_ID);
);
--Create TEAMS_AUDIT Table
CREATE TABLE Teams_Audit (
TA_ID int IDENTITY(100,1),
Team_ID int NOT NULL,
TA_NameOld varchar(100),--Old Team Name
TA_NameNew varchar(100),--New Team Name
TA_ProjectOld varchar(100),--Old Project Name
TA_ProjectNew varchar(100),--New Project Name
TA_SemIDOld int,--Old Semester ID
TA_SemIDNew int,--New Semester ID
TA_ModifiedBy varchar(255),
TA_ModifyDate datetime,
CONSTRAINT PK_Team_Audit
PRIMARY KEY NONCLUSTERED (TA_ID));
--Create Table Students
CREATE TABLE Students (
Student_ID int IDENTITY(100,1),
S_FName varchar(50) NOT NULL,
S_LName varchar(50) NOT NULL,
S_Role varchar(100) NOT NULL,
Team_ID int,
CONSTRAINT PK_Student_ID
PRIMARY KEY NONCLUSTERED (Student_ID),
CONSTRAINT FK_Student_Team_ID FOREIGN KEY (Team_ID)
REFERENCES Teams (Team_ID)
);
--Create STUDENTS_AUDIT Table
CREATE TABLE Students_Audit
(
SA_ID int IDENTITY(100,1),
Student_ID int NOT NULL,
SA_FNameOld varchar(100),--Old First Name
SA_FNameNew varchar(100),--New First Name
SA_LNameOld varchar(100),--Old Last Name
SA_LNameNew varchar(100),--New Last Name
SA_RoleOld varchar(100),--Old Role
SA_RoleNew varchar(100),--New Role
SA_TeamID_Old int,--Old Team ID (FK on Students Table)
SA_TeamID_New int,--New Team ID (FK on Students Table)
SA_ModifiedBy varchar(255),
SA_ModifyDate datetime,
CONSTRAINT PK_Students_Audit
PRIMARY KEY NONCLUSTERED (SA_ID));
--Create Table Links
CREATE TABLE Links (
Link_ID int IDENTITY(100,1),
Link_Title varchar(255) NOT NULL,
Link_URL varchar(MAX) NOT NULL,
Team_ID int NOT NULL,
CONSTRAINT PK_Link_ID
PRIMARY KEY NONCLUSTERED (Link_ID, Team_ID),
CONSTRAINT FK_Links_Team_ID FOREIGN KEY (Team_ID)
REFERENCES Teams (Team_ID)
);
--Create LINKS_AUDIT Table
CREATE TABLE Links_Audit
(
LA_ID int IDENTITY(100,1),
Link_ID int NOT NULL,
LA_TitleOld varchar(255),--Old Link Title
LA_TitleNew varchar(255),--New Link Title
LA_URL_Old varchar(MAX),--Old URL
LA_URL_New varchar(MAX),--New URL
LA_TeamID_Old int,--Old Team ID (FK on Links Table)
LA_TeamID_New int,--New Team ID (FK on Links Table)
LA_ModifiedBy varchar(255),
LA_ModifyDate datetime,
CONSTRAINT PK_Links_Audit
PRIMARY KEY NONCLUSTERED (LA_ID));
--Create Table Achievements
CREATE TABLE Achievements (
Achieve_ID int IDENTITY(100,1),
Achievement varchar(MAX) NOT NULL,
Team_ID int NOT NULL,
CONSTRAINT PK_Achieve_ID
PRIMARY KEY NONCLUSTERED (Achieve_ID),
CONSTRAINT FK_Achieve_Team_ID FOREIGN KEY (Team_ID)
REFERENCES Teams (Team_ID)
);
--Create ACHIEVEMENTS_AUDIT Table
CREATE TABLE Achievements_Audit
(
AA_ID int IDENTITY(100,1),
Achieve_ID int NOT NULL,
AA_AchieveOld varchar(MAX),--Old Achievements
AA_AchieveNew varchar(MAX),--New Achievements
AA_TeamID_Old int,--Old Team ID (FK on Achievements Table)
AA_TeamID_New int,--New Team ID (FK on Achievements Table)
AA_ModifiedBy varchar(255),
AA_ModifyDate datetime,
CONSTRAINT PK_Achieve_Audit
PRIMARY KEY NONCLUSTERED (AA_ID));
/*Triggers*/
--Create Trigger to update Users_Audit when record is Inserted
CREATE TRIGGER Tr_UsersAudit_Insert
ON Users
AFTER INSERT
AS
BEGIN
DECLARE @User_ID int
DECLARE @UA_EmailNew varchar(255)
DECLARE @UA_FNameNew varchar(100)
DECLARE @UA_LNameNew varchar(100)
DECLARE @UA_DOB_New date
DECLARE @UA_PW_New varchar(255)
DECLARE @UA_ModifyDate datetime
SELECT * INTO #UA_Temp FROM Inserted
WHILE (EXISTS (SELECT User_ID FROM #UA_Temp))
BEGIN
SELECT TOP 1
@User_ID = User_ID,
@UA_EmailNew = U_Email,
@UA_FNameNew = U_FName,
@UA_LNameNew = U_LName,
@UA_DOB_New = U_DOB,
@UA_PW_New = U_Password,
@UA_ModifyDate = U_CreateDate
FROM #UA_Temp
INSERT INTO Users_Audit (User_ID, UA_EmailNew, UA_FNameNew, UA_LNameNew, UA_DOB_New, UA_PW_New, UA_ModifiedBy, UA_ModifyDate)
VALUES (@User_ID, @UA_EmailNew, @UA_FNameNew, @UA_LNameNew, @UA_DOB_New, @UA_PW_New, @UA_EmailNew, @UA_ModifyDate)
DELETE FROM #UA_Temp
WHERE User_ID = @User_ID
END
END
GO
--Create Trigger to update Teams_Audit when record is Inserted
CREATE TRIGGER Tr_TeamsAudit_Insert
ON Teams
AFTER INSERT
AS
BEGIN
DECLARE @Team_ID int
DECLARE @TA_NameNew varchar(100)
DECLARE @TA_ProjectNew varchar(100)
DECLARE @TA_SemIDNew int
DECLARE @TA_ModifyDate datetime
SELECT * INTO #TA_Temp FROM Inserted
WHILE (EXISTS (SELECT Team_ID FROM #TA_Temp))
BEGIN
SELECT TOP 1
@Team_ID = Team_ID,
@TA_NameNew = Team_Name,
@TA_ProjectNew = Team_Project,
@TA_SemIDNew = Semester_ID,
@TA_ModifyDate = T_CreateDate
FROM #TA_Temp
INSERT INTO Teams_Audit (Team_ID, TA_NameNew, TA_ProjectNew, TA_SemIDNew, TA_ModifiedBy, TA_ModifyDate)
VALUES (@Team_ID, @TA_NameNew, @TA_ProjectNew, @TA_SemIDNew, '...', @TA_ModifyDate)
DELETE FROM #TA_Temp
WHERE Team_ID = @Team_ID
END
END
GO
--Create Trigger to update Students_Audit when record is Inserted
CREATE TRIGGER Tr_StudentsAudit_Insert
ON Students
AFTER INSERT
AS
BEGIN
DECLARE @Student_ID int
DECLARE @SA_FNameNew varchar(100)
DECLARE @SA_LNameNew varchar(100)
DECLARE @SA_RoleNew varchar(100)
DECLARE @SA_TeamID_New int
DECLARE @SA_ModifyDate datetime
SELECT * INTO #SA_Temp FROM Inserted
WHILE (EXISTS (SELECT Student_ID FROM #SA_Temp))
BEGIN
SELECT TOP 1
@Student_ID = Student_ID,
@SA_FNameNew = S_FName,
@SA_LNameNew = S_LName,
@SA_RoleNew = S_Role,
@SA_TeamID_New = Team_ID,
@SA_ModifyDate = S_CreateDate
FROM #SA_Temp
INSERT INTO Students_Audit (Student_ID, SA_FNameNew, SA_LNameNew, SA_RoleNew, SA_TeamID_New, SA_ModifiedBy, SA_ModifyDate)
VALUES (@Student_ID, @SA_FNameNew, @SA_LNameNew, @SA_RoleNew, @SA_TeamID_New, '...', @SA_ModifyDate)
DELETE FROM #SA_Temp
WHERE Student_ID = @Student_ID
END
END
GO
--Create Trigger to update Semesters_Audit when record is Inserted
CREATE TRIGGER Tr_SemestersAudit_Insert
ON Semesters
AFTER INSERT
AS
BEGIN
DECLARE @Semester_ID int
DECLARE @SemYear_New int
DECLARE @SemTerm_New varchar(10)
DECLARE @SemAud_ModifyDate datetime
SELECT * INTO #SemAud_Temp FROM Inserted
WHILE (EXISTS (SELECT Semester_ID FROM #SemAud_Temp))
BEGIN
SELECT TOP 1
@Semester_ID = Semester_ID,
@SemYear_New = Sem_Year,
@SemTerm_New = Sem_Term,
@SemAud_ModifyDate = Sem_CreateDate
FROM #SemAud_Temp
INSERT INTO Semesters_Audit (Semester_ID, SemYear_New, SemTerm_New, SemAud_ModifiedBy, SemAud_ModifyDate)
VALUES (@Semester_ID, @SemYear_New, @SemTerm_New, '...', @SemAud_ModifyDate)
DELETE FROM #SemAud_Temp
WHERE Semester_ID = @Semester_ID
END
END
GO
--Create Trigger to update Messages_Audit when record is Inserted
CREATE TRIGGER Tr_MessagesAudit_Insert
ON Messages
AFTER INSERT
AS
BEGIN
DECLARE @Message_ID int
DECLARE @MA_NameNew varchar(100)
DECLARE @MA_EmailNew varchar(100)
DECLARE @MA_PhoneNew varchar(25)
DECLARE @MA_MessageNew varchar(MAX)
DECLARE @MA_ModifyDate datetime
SELECT * INTO #MA_Temp FROM Inserted
WHILE (EXISTS (SELECT Message_ID FROM #MA_Temp))
BEGIN
SELECT TOP 1
@Message_ID = Message_ID,
@MA_NameNew = M_Name,
@MA_EmailNew = M_Email,
@MA_PhoneNew = M_Phone,
@MA_MessageNew = M_Message,
@MA_ModifyDate = M_CreateDate
FROM #MA_Temp
INSERT INTO Messages_Audit (Message_ID, MA_NameNew, MA_EmailNew, MA_PhoneNew, MA_MessageNew, MA_ModifiedBy, MA_ModifyDate)
VALUES (@Message_ID, @MA_NameNew, @MA_EmailNew, @MA_PhoneNew, @MA_MessageNew, @MA_EmailNew, @MA_ModifyDate)
DELETE FROM #MA_Temp
WHERE Message_ID = @Message_ID
END
END
GO
--Create Trigger to update Links_Audit when record is Inserted
CREATE TRIGGER Tr_LinksAudit_Insert
ON Links
AFTER INSERT
AS
BEGIN
DECLARE @Link_ID int
DECLARE @LA_TitleNew varchar(255)
DECLARE @LA_URL_New varchar(MAX)
DECLARE @LA_TeamID_New int
DECLARE @LA_ModifyDate datetime
SELECT * INTO #LA_Temp FROM Inserted
WHILE (EXISTS (SELECT Link_ID FROM #LA_Temp))
BEGIN
SELECT TOP 1
@Link_ID = Link_ID,
@LA_TitleNew = Link_Title,
@LA_URL_New = Link_URL,
@LA_TeamID_New = Team_ID,
@LA_ModifyDate = L_CreateDate
FROM #LA_Temp
INSERT INTO Links_Audit (Link_ID, LA_TitleNew, LA_URL_New, LA_TeamID_New, LA_ModifiedBy, LA_ModifyDate)
VALUES (@Link_ID, @LA_TitleNew, @LA_URL_New, @LA_TeamID_New, '...', @LA_ModifyDate)
DELETE FROM #LA_Temp
WHERE Link_ID = @Link_ID
END
END
GO
--Create Trigger to update Achievements_Audit when record is Inserted
CREATE TRIGGER Tr_AchievementsAudit_Insert
ON Achievements
AFTER INSERT
AS
BEGIN
DECLARE @Achieve_ID int
DECLARE @AA_AchieveNew varchar(MAX)
DECLARE @AA_TeamID_New int
DECLARE @AA_ModifyDate datetime
SELECT * INTO #AA_Temp FROM Inserted
WHILE (EXISTS (SELECT Achieve_ID FROM #AA_Temp))
BEGIN
SELECT TOP 1
@Achieve_ID = Achieve_ID,
@AA_AchieveNew = Achievement,
@AA_TeamID_New = Team_ID,
@AA_ModifyDate = A_CreateDate
FROM #AA_Temp
INSERT INTO Achievements_Audit (Achieve_ID, AA_AchieveNew, AA_TeamID_New, AA_ModifiedBy, AA_ModifyDate)
VALUES (@Achieve_ID, @AA_AchieveNew, @AA_TeamID_New, '...', @AA_ModifyDate)
DELETE FROM #AA_Temp
WHERE Achieve_ID = @Achieve_ID
END
END
GO
--Create Trigger to update Users_Audit when record is Deleted
CREATE TRIGGER Tr_UsersAudit_Delete
ON Users
AFTER Delete
AS
BEGIN
DECLARE @User_ID int
DECLARE @UA_EmailOld varchar(255)
DECLARE @UA_FNameOld varchar(100)
DECLARE @UA_LNameOld varchar(100)
DECLARE @UA_DOB_Old date
DECLARE @UA_PW_Old varchar(255)
SELECT * INTO #UA_Temp FROM Deleted
WHILE (EXISTS (SELECT User_ID FROM #UA_Temp))
BEGIN
SELECT TOP 1
@User_ID = User_ID,
@UA_EmailOld = U_Email,
@UA_FNameOld = U_FName,
@UA_LNameOld = U_LName,
@UA_DOB_Old = U_DOB,
@UA_PW_Old = U_Password
FROM #UA_Temp
CASE
WHEN UA_DOB_New = ''
THEN CAST(UA_DOB_New AS varchar(10))
END
INSERT INTO Users_Audit (User_ID, UA_EmailOld, UA_EmailNew, UA_FNameOld, UA_FNameNew, UA_LNameOld, UA_LNameNew, UA_DOB_Old, UA_DOB_New, UA_PW_Old, UA_PW_New, UA_ModifiedBy, UA_ModifyDate)
VALUES (@User_ID, @UA_EmailOld, 'DELETED', @UA_FNameOld, 'DELETED', @UA_LNameOld, 'DELETED', @UA_DOB_Old, (CONVERT(VARCHAR(10),'DELETED'), @UA_PW_Old, 'DELETED', '...', GetDate())
DELETE FROM #UA_Temp
WHERE User_ID = @User_ID
END
END
GO
--Create Trigger to update Teams_Audit when record is Deleted
CREATE TRIGGER Tr_TeamsAudit_Delete
ON Teams
AFTER Delete
AS
BEGIN
DECLARE @Team_ID int
DECLARE @TA_NameOld varchar(100)
DECLARE @TA_ProjectOld varchar(100)
DECLARE @TA_SemIDOld int
SELECT * INTO #TA_Temp FROM Deleted
WHILE (EXISTS (SELECT Team_ID FROM #TA_Temp))
BEGIN
SELECT TOP 1
@Team_ID = Team_ID,
@TA_NameOld = Team_Name,
@TA_ProjectOld = Team_Project,
@TA_SemIDOld = Semester_ID
FROM #TA_Temp
CASE
WHEN TA_SemID_New = ''
THEN CAST(TA_SemID_New AS varchar(10))
END
INSERT INTO Teams_Audit (Team_ID, TA_NameOld, TA_NameNew, TA_ProjectOld, TA_ProjectNew, TA_SemIDOld, TA_SemIDNew, TA_ModifiedBy, TA_ModifyDate)
VALUES (@Team_ID, @TA_NameOld, 'DELETED', @TA_ProjectOld, 'DELETED', @TA_SemIDOld, (CONVERT(VARCHAR(10),'DELETED'), '...', GetDate())
DELETE FROM #TA_Temp
WHERE Team_ID = @Team_ID
END
END
GO
--Create Trigger to update Students_Audit when record is Deleted
CREATE TRIGGER Tr_StudentsAudit_Delete
ON Students
AFTER Delete
AS
BEGIN
DECLARE @Student_ID int
DECLARE @SA_FNameOld varchar(100)
DECLARE @SA_LNameOld varchar(100)
DECLARE @SA_RoleOld varchar(100)
DECLARE @SA_TeamID_Old int
SELECT * INTO #SA_Temp FROM Deleted
WHILE (EXISTS (SELECT Student_ID FROM #SA_Temp))
BEGIN
SELECT TOP 1
@Student_ID = Student_ID,
@SA_FNameOld = S_FName,
@SA_LNameOld = S_LName,
@SA_RoleOld = S_Role,
@SA_TeamID_Old = Team_ID
FROM #SA_Temp
CASE
WHEN SA_TeamID_New = ''
THEN CAST(SA_TeamID_New AS varchar(10))
END
INSERT INTO Students_Audit (Student_ID, SA_FNameOld, SA_FNameNew, SA_LNameOld, SA_LNameNew, SA_RoleOld, SA_RoleNew, SA_TeamID_Old, SA_TeamID_New, SA_ModifiedBy, SA_ModifyDate)
VALUES (@Student_ID, @SA_FNameOld, 'DELETED', @SA_LNameOld, 'DELETED', @SA_RoleOld, 'DELETED', @SA_TeamID_Old, (CONVERT(VARCHAR(10),'DELETED'), '...', GetDate())
DELETE FROM #SA_Temp
WHERE Student_ID = @Student_ID
END
END
GO
--Create Trigger to update Semesters_Audit when record is Deleted
CREATE TRIGGER Tr_SemestersAudit_Delete
ON Semesters
AFTER Delete
AS
BEGIN
DECLARE @Semester_ID int
DECLARE @SemYear_Old int
DECLARE @SemTerm_Old varchar(10)
DECLARE @SemAud_ModifyDate datetime
SELECT * INTO #SemAud_Temp FROM Deleted
WHILE (EXISTS (SELECT Semester_ID FROM #SemAud_Temp))
BEGIN
SELECT TOP 1
@Semester_ID = Semester_ID,
@SemYear_Old = Sem_Year,
@SemTerm_Old = Sem_Term,
@SemAud_ModifyDate = Sem_CreateDate
FROM #SemAud_Temp
CASE
WHEN SemYear_New = ''
THEN CAST(SemYear_New AS varchar(10))
END
INSERT INTO Semesters_Audit (Semester_ID, SemYear_Old, SemYear_New, SemTerm_Old, SemTerm_New, SemAud_ModifiedBy, SemAud_ModifyDate)
VALUES (@Semester_ID, @SemYear_Old, (CONVERT(VARCHAR(10),'DELETED'), @SemTerm_Old, 'DELETED', '...', GetDate())
DELETE FROM #SemAud_Temp
WHERE Semester_ID = @Semester_ID
END
END
GO
--Create Trigger to update Messages_Audit when record is Deleted
CREATE TRIGGER Tr_MessagesAudit_Delete
ON Messages
AFTER Delete
AS
BEGIN
DECLARE @Message_ID int
DECLARE @MA_NameOld varchar(100)
DECLARE @MA_EmailOld varchar(100)
DECLARE @MA_PhoneOld varchar(25)
DECLARE @MA_MessageOld varchar(MAX)
SELECT * INTO #MA_Temp FROM Deleted
WHILE (EXISTS (SELECT Message_ID FROM #MA_Temp))
BEGIN
SELECT TOP 1
@Message_ID = Message_ID,
@MA_NameOld = M_Name,
@MA_EmailOld = M_Email,
@MA_PhoneOld = M_Phone,
@MA_MessageOld = M_Message
FROM #MA_Temp
INSERT INTO Messages_Audit (Message_ID, MA_NameOld, MA_NameNew, MA_EmailOld, MA_EmailNew, MA_PhoneOld, MA_PhoneNew, MA_MessageOld, MA_MessageNew, MA_ModifiedBy, MA_ModifyDate)
VALUES (@Message_ID, @MA_NameOld, 'DELETED', @MA_EmailOld, 'DELETED', @MA_PhoneOld, 'DELETED', @MA_MessageOld, 'DELETED', '...', GetDate())
DELETE FROM #MA_Temp
WHERE Message_ID = @Message_ID
END
END
GO
--Create Trigger to update Links_Audit when record is Deleted
CREATE TRIGGER Tr_LinksAudit_Delete
ON Links
AFTER Delete
AS
BEGIN
DECLARE @Link_ID int
DECLARE @LA_TitleOld varchar(255)
DECLARE @LA_URL_Old varchar(MAX)
DECLARE @LA_TeamID_Old int
SELECT * INTO #LA_Temp FROM Deleted
WHILE (EXISTS (SELECT Link_ID FROM #LA_Temp))
BEGIN
SELECT TOP 1
@Link_ID = Link_ID,
@LA_TitleOld = Link_Title,
@LA_URL_Old = Link_URL,
@LA_TeamID_Old = Team_ID
FROM #LA_Temp
CASE
WHEN LA_TeamID_New = ''
THEN CAST(LA_TeamID_New AS varchar(10))
END
INSERT INTO Links_Audit (Link_ID, LA_TitleOld, LA_TitleNew, LA_URL_Old, LA_URL_New, LA_TeamID_Old, LA_TeamID_New, LA_ModifiedBy, LA_ModifyDate)
VALUES (@Link_ID, @LA_TitleOld, 'DELETED', @LA_URL_Old, 'DELETED', @LA_TeamID_Old, 'DELETED', '...', GetDate())
DELETE FROM #LA_Temp
WHERE Link_ID = @Link_ID
END
END
GO
--Create Trigger to update Achievements_Audit when record is Deleted
CREATE TRIGGER Tr_AchievementsAudit_Delete
ON Achievements
AFTER Delete
AS
BEGIN
DECLARE @Achieve_ID int
DECLARE @AA_AchieveOld varchar(MAX)
DECLARE @AA_TeamID_Old int
SELECT * INTO #AA_Temp FROM Deleted
WHILE (EXISTS (SELECT Achieve_ID FROM #AA_Temp))
BEGIN
SELECT TOP 1
@Achieve_ID = Achieve_ID,
@AA_AchieveOld = Achievement,
@AA_TeamID_Old = Team_ID
FROM #AA_Temp
INSERT INTO Achievements_Audit (Achieve_ID, AA_AchieveOld, AA_AchieveNew, AA_TeamID_Old, AA_TeamID_New, AA_ModifiedBy, AA_ModifyDate)
VALUES (@Achieve_ID, @AA_AchieveOld, 'DELETED', @AA_TeamID_Old, (CONVERT(VARCHAR(10),'DELETED'), '...', GetDate())
DELETE FROM #AA_Temp
WHERE Achieve_ID = @Achieve_ID
END
END
GO
--Create Trigger to update Users_Audit when record is Updated
CREATE TRIGGER Tr_UsersAudit_Update
ON Users
AFTER UPDATE
AS
BEGIN
DECLARE @User_ID int
DECLARE @UA_EmailOld varchar(255)
DECLARE @UA_EmailNew varchar(255)
DECLARE @UA_FNameOld varchar(100)
DECLARE @UA_FNameNew varchar(100)
DECLARE @UA_LNameOld varchar(100)
DECLARE @UA_LNameNew varchar(100)
DECLARE @UA_DOB_Old date
DECLARE @UA_DOB_New date
DECLARE @UA_PW_Old varchar(255)
DECLARE @UA_PW_New varchar(255)
SELECT * INTO #UA_Temp FROM Deleted
WHILE (EXISTS (SELECT User_ID FROM #UA_Temp))
BEGIN
SELECT TOP 1
@User_ID = User_ID,
@UA_EmailOld = U_Email,
@UA_FNameOld = U_FName,
@UA_LNameOld = U_LName,
@UA_DOB_Old = U_DOB,
@UA_PW_Old = U_Password
FROM #UA_Temp
SELECT
@User_ID = User_ID,
@UA_EmailNew = U_Email,
@UA_FNameNew = U_FName,
@UA_LNameNew = U_LName,
@UA_DOB_New = U_DOB,
@UA_PW_New = U_Password
FROM Inserted
WHERE User_ID = @User_ID
IF (@UA_EmailOld = @UA_EmailNew)
BEGIN
SET @UA_EmailOld = 'N/A'
SET @UA_EmailNew = 'N/A'
END
IF (@UA_FNameOld = @UA_FNameNew)
BEGIN
SET @UA_FNameOld = 'N/A'
SET @UA_FNameNew = 'N/A'
END
IF (@UA_LNameOld = @UA_LNameNew)
BEGIN
SET @UA_LNameOld = 'N/A'
SET @UA_LNameNew = 'N/A'
END
IF (@UA_DOB_Old = @UA_DOB_New)
BEGIN
SET @UA_DOB_Old = 'N/A'
SET @UA_DOB_New = 'N/A'
END
IF (@UA_PW_Old = @UA_PW_New)
BEGIN
SET @UA_PW_Old = 'N/A'
SET @UA_PW_New = 'N/A'
END
INSERT INTO Users_Audit
(User_ID, UA_EmailOld, UA_EmailNew, UA_FNameOld, UA_FNameNew, UA_LNameOld, UA_LNameNew, UA_DOB_Old, UA_DOB_New, UA_PW_Old, UA_PW_New, UA_ModifiedBy, UA_ModifyDate)
VALUES
(@User_ID, @UA_EmailOld, @UA_EmailNew, @UA_FNameOld, @UA_FNameNew, @UA_LNameOld, @UA_LNameNew, @UA_DOB_Old, @UA_DOB_New, @UA_PW_Old, @UA_PW_New, @UA_EmailNew, GetDate())
DELETE FROM #UA_Temp
WHERE User_ID = @User_ID
END
END
GO
--Create Trigger to update Teams_Audit when record is Updated
CREATE TRIGGER Tr_TeamsAudit_Update
ON Teams
AFTER UPDATE
AS
BEGIN
DECLARE @Team_ID int
DECLARE @TA_NameOld varchar(100)
DECLARE @TA_NameNew varchar(100)
DECLARE @TA_ProjectOld varchar(100)
DECLARE @TA_ProjectNew varchar(100)
DECLARE @TA_SemIDOld int
DECLARE @TA_SemIDNew int
SELECT * INTO #TA_Temp FROM Deleted
WHILE (EXISTS (SELECT Team_ID FROM #TA_Temp))
BEGIN
SELECT TOP 1
@Team_ID = Team_ID,
@TA_NameOld = Team_Name,
@TA_ProjectOld = Team_Project,
@TA_SemIDOld = Semester_ID
FROM #TA_Temp
SELECT
@Team_ID = Team_ID,
@TA_NameNew = Team_Name,
@TA_ProjectNew = Team_Project,
@TA_SemIDNew = Semester_ID
FROM Inserted
WHERE Team_ID = @Team_ID
IF (@TA_NameOld = @TA_NameNew)
BEGIN
SET @TA_NameOld = 'N/A'
SET @TA_NameNew = 'N/A'
END
IF (@TA_ProjectOld = @TA_ProjectNew)
BEGIN
SET @TA_ProjectOld = 'N/A'
SET @TA_ProjectNew = 'N/A'
END
IF (@TA_SemIDOld = @TA_SemIDNew)
BEGIN
SET @TA_SemIDOld = 'N/A'
SET @TA_SemIDNew = 'N/A'
END
INSERT INTO Teams_Audit
(Team_ID, TA_NameOld, TA_NameNew, TA_ProjectOld, TA_ProjectNew, TA_SemIDOld, TA_SemIDNew, TA_ModifiedBy, TA_ModifyDate)
VALUES
(@Team_ID, @TA_NameOld, @TA_NameNew, @TA_ProjectOld, @TA_ProjectNew, @TA_SemIDOld, @TA_SemIDNew, '...', GetDate())
DELETE FROM #TA_Temp
WHERE Team_ID = @Team_ID
END
END
GO
--Create Trigger to update Students_Audit when record is Updated
CREATE TRIGGER Tr_StudentsAudit_Update
ON Students
AFTER UPDATE
AS
BEGIN
DECLARE @Student_ID int
DECLARE @SA_FNameOld varchar(100)
DECLARE @SA_FNameNew varchar(100)
DECLARE @SA_LNameOld varchar(100)
DECLARE @SA_LNameNew varchar(100)
DECLARE @SA_RoleOld varchar(100)
DECLARE @SA_RoleNew varchar(100)
DECLARE @SA_TeamID_Old int
DECLARE @SA_TeamID_New int
SELECT * INTO #SA_Temp FROM Deleted
WHILE (EXISTS (SELECT Student_ID FROM #SA_Temp))
BEGIN
SELECT TOP 1
@Student_ID = Student_ID,
@SA_FNameOld = S_FName,
@SA_LNameOld = S_LName,
@SA_RoleOld = S_Role,
@SA_TeamID_Old = Team_ID
FROM #SA_Temp
SELECT
@Student_ID = Student_ID,
@SA_FNameNew = S_FName,
@SA_LNameNew = S_LName,
@SA_RoleNew = S_Role,
@SA_TeamID_New = Team_ID
FROM Inserted
WHERE Student_ID = @Student_ID
IF (@SA_FNameOld = @SA_FNameNew)
BEGIN
SET @SA_FNameOld = 'N/A'
SET @SA_FNameNew = 'N/A'
END
IF (@SA_LNameOld = @SA_LNameNew)
BEGIN
SET @SA_LNameOld = 'N/A'
SET @SA_LNameNew = 'N/A'
END
IF (@SA_RoleOld = @SA_RoleNew)
BEGIN
SET @SA_RoleOld = 'N/A'
SET @SA_RoleNew = 'N/A'
END
IF (@SA_TeamID_Old = @SA_TeamID_New)
BEGIN
SET @SA_TeamID_Old = 'N/A'
SET @SA_TeamID_New = 'N/A'
END
INSERT INTO Students_Audit
(Student_ID, SA_FNameOld, SA_FNameNew, SA_LNameOld, SA_LNameNew, SA_RoleOld, SA_RoleNew, SA_TeamID_Old, SA_TeamID_New, SA_ModifiedBy, SA_ModifyDate)
VALUES
(@Student_ID, @SA_FNameOld, @SA_FNameNew, @SA_LNameOld, @SA_LNameNew, @SA_RoleOld, @SA_RoleNew, @SA_TeamID_Old, @SA_TeamID_Old, '...', GetDate())
DELETE FROM #SA_Temp
WHERE Student_ID = @Student_ID
END
END
GO
--Create Trigger to update Semesters_Audit when record is Updated
CREATE TRIGGER Tr_SemestersAudit_Update
ON Semesters
AFTER UPDATE
AS
BEGIN
DECLARE @Semester_ID int
DECLARE @SemYear_Old int
DECLARE @SemYear_New int
DECLARE @SemTerm_Old varchar(10)
DECLARE @SemTerm_New varchar(10)
SELECT * INTO #SemAud_Temp FROM Deleted
WHILE (EXISTS (SELECT Semester_ID FROM #SemAud_Temp))
BEGIN
SELECT TOP 1
@Semester_ID = Semester_ID,
@SemYear_Old = Sem_Year,
@SemTerm_Old = Sem_Term
FROM #SemAud_Temp
SELECT
@Semester_ID = Semester_ID,
@SemYear_New = Sem_Year,
@SemTerm_New = Sem_Term
FROM Inserted
WHERE Semester_ID = @Semester_ID
IF (@SemYear_Old = @SemYear_New)
BEGIN
SET @SemYear_Old = 'N/A'
SET @SemYear_New = 'N/A'
END
IF (@SemTerm_Old = @SemTerm_New)
BEGIN
SET @SemTerm_Old = 'N/A'
SET @SemTerm_New = 'N/A'
END
INSERT INTO Semesters_Audit
(Semester_ID, SemYear_Old, SemYear_New, SemTerm_Old, SemTerm_New, SemAud_ModifiedBy, SemAud_ModifyDate)
VALUES
(@Semester_ID, @SemYear_Old, @SemYear_New, @SemTerm_Old, @SemTerm_New, '...', GetDate())
DELETE FROM #SemAud_Temp
WHERE Semester_ID = @Semester_ID
END
END
GO
--Create Trigger to update Messages_Audit when record is Updated
CREATE TRIGGER Tr_MessagesAudit_Update
ON Messages
AFTER UPDATE
AS
BEGIN
DECLARE @Message_ID int
DECLARE @MA_NameOld varchar(100)
DECLARE @MA_NameNew varchar(100)
DECLARE @MA_EmailOld varchar(100)
DECLARE @MA_EmailNew varchar(100)
DECLARE @MA_PhoneOld varchar(25)
DECLARE @MA_PhoneNew varchar(25)
DECLARE @MA_MessageOld varchar(MAX)
DECLARE @MA_MessageNew varchar(MAX)
SELECT * INTO #MA_Temp FROM Deleted
WHILE (EXISTS (SELECT Message_ID FROM #MA_Temp))
BEGIN
SELECT TOP 1
@Message_ID = Message_ID,
@MA_NameOld = M_Name,
@MA_EmailOld = M_Email,
@MA_PhoneOld = M_Phone,
@MA_MessageOld = M_Message
FROM #MA_Temp
SELECT
@Message_ID = Message_ID,
@MA_NameNew = M_Name,
@MA_EmailNew = M_Email,
@MA_PhoneNew = M_Phone,
@MA_MessageNew = M_Message
FROM Inserted
WHERE Message_ID = @Message_ID
IF (@MA_NameOld = @MA_NameNew)
BEGIN
SET @MA_NameOld = 'N/A'
SET @MA_NameNew = 'N/A'
END
IF (@MA_EmailOld = @MA_EmailNew)
BEGIN
SET @MA_EmailOld = 'N/A'
SET @MA_EmailNew = 'N/A'
END
IF (@MA_PhoneOld = @MA_PhoneNew)
BEGIN
SET @MA_PhoneOld = 'N/A'
SET @MA_PhoneNew = 'N/A'
END
IF (@MA_MessageOld = @MA_MessageNew)
BEGIN
SET @MA_MessageOld = 'N/A'
SET @MA_MessageNew = 'N/A'
END
INSERT INTO Messages_Audit (Message_ID, MA_NameOld, MA_NameNew, MA_EmailOld, MA_EmailNew, MA_PhoneOld, MA_PhoneNew, MA_MessageOld, MA_MessageNew, MA_ModifiedBy, MA_ModifyDate)
VALUES (@Message_ID, @MA_NameOld, @MA_NameNew, @MA_EmailOld, @MA_EmailNew, @MA_PhoneOld, @MA_PhoneNew, @MA_MessageOld, @MA_MessageNew, @MA_EmailNew, GetDate())
DELETE FROM #MA_Temp
WHERE Message_ID = @Message_ID
END
END
GO
--Create Trigger to update Links_Audit when record is Updated
CREATE TRIGGER Tr_LinksAudit_Update
ON Links
AFTER UPDATE
AS
BEGIN
DECLARE @Link_ID int
DECLARE @LA_TitleOld varchar(255)
DECLARE @LA_TitleNew varchar(255)
DECLARE @LA_URL_Old varchar(MAX)
DECLARE @LA_URL_New varchar(MAX)
DECLARE @LA_TeamID_Old int
DECLARE @LA_TeamID_New int
SELECT * INTO #LA_Temp FROM Deleted
WHILE (EXISTS (SELECT Link_ID FROM #LA_Temp_I))
BEGIN
SELECT TOP 1
@Link_ID = Link_ID,
@LA_TitleOld = Link_Title,
@LA_URL_Old = Link_URL,
@LA_TeamID_Old = Team_ID
FROM #LA_Temp
SELECT
@LA_TitleNew = Link_Title,
@LA_URL_New = Link_URL,
@LA_TeamID_New = Team_ID
FROM Inserted
WHERE Link_ID = @Link_ID
IF (@LA_TitleOld = @LA_TitleNew)
BEGIN
SET @LA_TitleOld = 'N/A'
SET @LA_TitleNew = 'N/A'
END
IF (@LA_URL_Old = @LA_URL_New)
BEGIN
SET @LA_URL_Old = 'N/A'
SET @LA_URL_New = 'N/A'
END
IF (@LA_TeamID_Old = @LA_TeamID_New)
BEGIN
SET @LA_TeamID_Old = 'N/A'
SET @LA_TeamID_New = 'N/A'
END
INSERT INTO Links_Audit
(Link_ID, LA_TitleOld, LA_TitleNew, LA_URL_Old, LA_URL_New, LA_TeamID_Old, LA_TeamID_New, LA_ModifiedBy, LA_ModifyDate)
VALUES
(@Link_ID, @LA_TitleOld, @LA_TitleNew, @LA_URL_Old, @LA_URL_New, @LA_TeamID_Old, @LA_TeamID_New, '...', GetDate())
DELETE FROM #LA_Temp
WHERE Link_ID = @Link_ID
END
END
GO
--Create Trigger to update Achievements_Audit when record is Updated
CREATE TRIGGER Tr_AchievementsAudit_Update
ON Achievements
AFTER UPDATE
AS
BEGIN
DECLARE @Achieve_ID int
DECLARE @AA_AchieveOld varchar(MAX)
DECLARE @AA_AchieveNew varchar(MAX)
DECLARE @AA_TeamID_Old int
DECLARE @AA_TeamID_New int
SELECT * INTO #AA_Temp FROM Deleted
WHILE (EXISTS (SELECT Achieve_ID FROM #AA_Temp))
BEGIN
SELECT TOP 1
@Achieve_ID = Achieve_ID,
@AA_AchieveOld = Achievement,
@AA_TeamID_Old = Team_ID
FROM #AA_Temp
SELECT
@AA_AchieveNew = Achievement,
@AA_TeamID_New = Team_ID
FROM Inserted
WHERE Achieve_ID = @Achieve_ID
IF (@AA_AchieveOld = @AA_AchieveNew)
BEGIN
SET @AA_AchieveOld = 'N/A'
SET @AA_AchieveNew = 'N/A'
END
IF (@AA_TeamID_Old = @AA_TeamID_New)
BEGIN
SET @AA_TeamID_Old = 'N/A'
SET @AA_TeamID_New = 'N/A'
END
INSERT INTO Achievements_Audit (Achieve_ID, AA_AchieveOld, AA_AchieveNew, AA_TeamID_Old, AA_TeamID_New, AA_ModifiedBy, AA_ModifyDate)
VALUES (@Achieve_ID, @AA_AchieveOld, @AA_AchieveNew, @AA_TeamID_Old, @AA_TeamID_New, '...', GetDate())
DELETE FROM #AA_Temp
WHERE Achieve_ID = @Achieve_ID
END
END
GO
November 22, 2020 at 6:36 pm
OK, let me start by answering your questions/concerns:
- The Trigger methodology I am using is what wasa showed to me. I figured it would be good for a "first try" at developing triggers.
- The reason I selected varchar(MAX) for the Achievements column is because it is supposed to list what each team did while working on their respective parts of the project that was assigned to them. Until I have an idea of how big that information will be, I felt it best to use varchar(MAX) over a set number.
- No, achievements are not being stored as CSV.
- Again, if I knew what kind of Achievements we were going to have, I'd consider normalization instead.
- You want the CREATE TABLE statements? Ask and ye shall receive!
A bit over the top, I'd say. I asked only for the CREATE TABLE statements for the Achievements and Achievements_Audit tables but thank you for posting all of it because it does identify that there's some pretty serious issues going on (and I do appreciate that you're really trying) even if I don't bring up the worst practice of using While loops and other forms of RBAR in trigger code.
Shifting gears a bit, who or what the hell is "wasa" and how can I reach them because, if this is a true example of what they're doing, I need to contact them so they can fix a bunch of stuff. 😉 And, yeah... I'm dead serious about that. "wasa" needs a bit of "calibration" if this is actually the kind of thing they've documented or are promoting as "right" because it's far from it.
Moving back to the subject at hand, from what I can see in the trigger, you've not yet finished designing the either of those two tables. For example, the Tr_AchievementsAudit_Insert trigger references a column called A_CreateDate from your Temp table (which I aim to help you get rid of) but there is no such column in the Achievements base table.
If we look at the rest of your defined tables, some have a CreateDate and some do not.
Looking at your base and audit tables and the related triggers, I'll also state the following.
All that being said, I'm thinking that you really want what "Temporal Tables" has to offer with one major fault there... they don't capture who did the modification unless you have it in your base table and then enforcement of what is contained in that column is up to you which, of course, would require a trigger to be sure.
Basically, what most people want is the functionality provided by a "Pure Type 6 SCD" (Slowly Changing Dimension) but with the addition of who change what/when. It can all be done in the base table (please don't even think about using an "IsCurrent" column... it's just one more thing that would need to be updates) but that also means that your history is stored in the same place as the current data and that can make for some slow queries. So a "Pure Type 6 SCD" with the current data in the base table and the historic data in an audit table is what most people (my observation only) end up with.
https://en.wikipedia.org/wiki/Slowly_changing_dimension#Pure_type_6_implementation
In my interpretation of a "Pure Type 6 SCD", each base and audit table will need a ModificationStartDate and a ModificationEndDate column and both are NOT null. You also need a not null ModifiedBy column in both tables. All 3 of these columns will be enforced in the base table by a trigger. In the base table, ModificationEndDate will always be '9999-01-01 00:00:00.000" (the extra space 'til 9999/12/31 allows for date searching tricks).
With all that being said, what do you actually want to do? If you want to record both the old'n'new values in the audit table and audit inserts, I'm so dead set against such a thing that I'll just move on leaving you with my strong warning the it's grossly ineffective and grossly inefficient and you and your server will be in a world a hurt down the road if you implement such methodology.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2020 at 8:09 pm
A bit over the top, I'd say. I asked only for the CREATE TABLE statements for the Achievements and Achievements_Audit tables but thank you for posting all of it because it does identify that there's some pretty serious issues going on (and I do appreciate that you're really trying) even if I don't bring up the worst practice of using While loops and other forms of RBAR in trigger code.
I wanted to make sure you had the full picture of what I was working on. And I thank you for showing appreciation in my attempt. I saw something Adam Savage recently posted on a "Tested" video where he talked about makers showing their failures and it helps engender people into the movement because they can watch those failures and not think their own failures are an end-all/be-all situation. Besides, I'm sure that you and other on here made your own share of mistakes when first learning how to program in SQL (or any other programming language for that matter)
Shifting gears a bit, who or what the hell is "wasa" and how can I reach them because, if this is a true example of what they're doing, I need to contact them so they can fix a bunch of stuff. And, yeah... I'm dead serious about that. "wasa" needs a bit of "calibration" if this is actually the kind of thing they've documented or are promoting as "right" because it's far from it.
Ooops ... typo! LOL
Moving back to the subject at hand, from what I can see in the trigger, you've not yet finished designing the either of those two tables. For example, the Tr_AchievementsAudit_Insert trigger references a column called A_CreateDate from your Temp table (which I aim to help you get rid of) but there is no such column in the Achievements base table.
If we look at the rest of your defined tables, some have a CreateDate and some do not.
I do have a CreateDate column, but I had to add it after the fact using ALTER TABLE, as the initial creation code was already sent to the Development team to implement into the "production" database.
Looking at your base and audit tables and the related triggers, I'll also state the following.
Auditing INSERTs is totally unnecessary. If a row is inserted into the base table and never changes, the "audit" for that row is actually the row in the base table. Actually causing a row to appear in the audit table during an insert into the base table is also a gross duplication of data and even if you never update a row anywhere in your database, auditing inserts will cause the database to be more than twice the size it needs to be.
Fair enough. I will take those out, which will be easy enough to do since they are at the top of the Triggers code.
Auditing old'n'new values for row based audits is yet another totally unnecessary duplication of data.
Despite your efforts to make life easier through the extreme and totally unnecessary duplication of data, none of it will make it easy for you to do a "point in time" status of what the conditions for even a single row were on a given date.
All that being said, I'm thinking that you really want what "Temporal Tables" has to offer with one major fault there... they don't capture who did the modification unless you have it in your base table and then enforcement of what is contained in that column is up to you which, of course, would require a trigger to be sure.
In a nutshell, I want to make sure that if data is accidentally deleted or updated that it can be restored. I'm not familiar/comfortable enough with BEGIN TRANS/ROLLBACK TRANS to get that coded into the database in case something like that were to happen.
Basically, what most people want is the functionality provided by a "Pure Type 6 SCD" (Slowly Changing Dimension) but with the addition of who change what/when. It can all be done in the base table (please don't even think about using an "IsCurrent" column... it's just one more thing that would need to be updates) but that also means that your history is stored in the same place as the current data and that can make for some slow queries. So a "Pure Type 6 SCD" with the current data in the base table and the historic data in an audit table is what most people (my observation only) end up with.
https://en.wikipedia.org/wiki/Slowly_changing_dimension#Pure_type_6_implementation
In my interpretation of a "Pure Type 6 SCD", each base and audit table will need a ModificationStartDate and a ModificationEndDate column and both are NOT null. You also need a not null ModifiedBy column in both tables. All 3 of these columns will be enforced in the base table by a trigger. In the base table, ModificationEndDate will always be '9999-01-01 00:00:00.000" (the extra space 'til 9999/12/31 allows for date searching tricks).
I did a quick skim of what this is about and this makes sense. I don't know if I need a ModifiedBy column because, now that I think about it, I don't believe we have much of the data being updated by just anyone. To be honest, I have no idea who will be updating these tables as time goes along.
With all that being said, what do you actually want to do? If you want to record both the old'n'new values in the audit table and audit inserts, I'm so dead set against such a thing that I'll just move on leaving you with my strong warning the it's grossly ineffective and grossly inefficient and you and your server will be in a world a hurt down the road if you implement such methodology.
As I said earlier in the post, my focus was on having some way of tracking insertions, deletions (which I'm willing to instead make the record inactive, if possible), and changes (what few there will or may be) in the data. I initially chose triggers because this is for a class and I wanted to show that I knew more than just how to create and alter tables and constraints. I would not have posted my question here if I didn't want to come up with some kind of alternative/better method to achieve things. I know I could have gone to my instructor for this (and he has been more than adequate in helping me learn some things I didn't really know a lot about at the time), but I thought that 1) I had a better grasp on things and 2) I was afraid that he would take too long to get back with me on potential options.
BTW, I have no qualms about sending a brand new SQL Script to the developers and telling them to "blow up" (read: rename) the current database and start again from scratch. Honestly, I'd rather do that over sending them multiple queries to build/modify the existing data structure.
November 23, 2020 at 4:14 am
Based on what you said about being able to recover from bad deletes and updates, my recommendation would be to setup the tables for System-Versioned Temporal Tables. That means that you don't have to write the triggers nor create the audit tables (it'll do all that for you) and it'll take care a Pure Type 6 SCD for you.
I'm also a bit concerned about the rest of your tables because they're "Heaps" (have no Clustered Index). "ExpAnsive" updates can be pretty tough on Heaps due to row forwarding and the absence of Clustered Indexes can cause some pretty good slowdowns for your SELECTs. Of course, the opposite is also sometimes true and with the idea that "It Depends", you might want to try adding well thought out Clustered Indexes to the tables.
Heh... and, yeah, I'm going to get after you on this... you and the people that asked for this are putting the company you work for at risk.
U_Password varchar(255) NOT NULL, /*--Don't get after me about this ....*/
You might also want to normalize your Users table... that email address column should be in a separate table with a bridge/mapping table between the two.
Same thing goes with the Teams table. Having a project and semester column in there is going to work for about 1 semester in a row.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2020 at 4:44 am
Based on what you said about being able to recover from bad deletes and updates, my recommendation would be to setup the tables for System-Versioned Temporal Tables. That means that you don't have to write the triggers nor create the audit tables (it'll do all that for you) and it'll take care a Pure Type 6 SCD for you.
I appreciate that recommendation. Unfortunately, this was something we were not taught during my class. So, I'm going into it literally cold. I'll see what I can find via YouTube, but would appreciate additional input. I am willing and able to meet via Zoom or Google Meet to discuss this if you are willing and/or able.
I'm also a bit concerned about the rest of your tables because they're "Heaps" (have no Clustered Index). "ExpAnsive" updates can be pretty tough on Heaps due to row forwarding and the absence of Clustered Indexes can cause some pretty good slowdowns for your SELECTs. Of course, the opposite is also sometimes true and with the idea that "It Depends", you might want to try adding well thought out Clustered Indexes to the tables.
I have Clustered Indexes created; I just forgot to include them when I posted my code. If you want to see them, I'll be happy to post them.
Heh... and, yeah, I'm going to get after you on this... you and the people that asked for this are putting the company you work for at risk.
U_Password varchar(255) NOT NULL, /*--Don't get after me about this ....*/
Unless I or the developers can come up with some way to hash this (and I am more than happy to change the column name to help with security), I don't know what other options I have.
You might also want to normalize your Users table... that email address column should be in a separate table with a bridge/mapping table between the two.
So have Name and Email Address on one table then everything else in a Users Table? I just want to make sure I understand what you are saying.
Same thing goes with the Teams table. Having a project and semester column in there is going to work for about 1 semester in a row.
I had considered doing this (creating a project table and adding a FK link to the Teams table) but decided against it. I'll be happy to alleviate that issue. The Semester column is already set up as a PK/FK link (Semester_ID in Teams links to Semester_ID in the Semesters table).
November 23, 2020 at 5:42 am
I previously provided you with a link to the Temporal Tables documentation and it has examples, etc, with different links for different things. Of course, if you want the quick'n'dirty from a 'tube, that'll work but everything you need is in the link I provided. As a bit of a sidebar, did they actually teach audit triggers as a part of the class? If so and as I said before, I seriously question their methods.
On the user email thing, you'd have one table with email addresses and another with users (which you already have). Nothing in either table would reference the other table. Instead, there'd be a table between them with two columns. One for user ids and the other for email ids... a mapping table. That would allow people to have more than one email address and the same email address to serve more than one person.
On the password thing, I recommend a one way password with "salt" using one of the more robust algorithms available to the HASHBYTES function at the very least.
Since this is a class, are they using some text book to teach from? If so, do you have an ISBN you could share?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply