IDENTITY only allowed on one table at a time?

  • 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?

  • Can you please post the full DDL for all the tables, including all keys and constraints?

    😎

     

  • 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.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

    • This reply was modified 3 years, 4 months ago by  thetubageek.
    • This reply was modified 3 years, 4 months ago by  thetubageek.
    • This reply was modified 3 years, 4 months ago by  thetubageek.
  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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!
    --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

    • This reply was modified 3 years, 4 months ago by  thetubageek.
  • thetubageek wrote:

    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.

    1. 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.
    2. Auditing old'n'new values for row based audits is yet another totally unnecessary duplication of data.
    3. 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.

    https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

    https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

    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.

    • This reply was modified 3 years, 4 months ago by  thetubageek.
  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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).

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply