TR_SiteContent_INSERT_UPDATE (Trigger)

                
            CREATE TRIGGER [dbo].[TR_SiteContent_INSERT_UPDATE]
            ON [dbo].[SiteContent]
            FOR INSERT, UPDATE
            AS

            BEGIN TRANSACTION

                DECLARE @DeprecatedInserts AS int
                
                SELECT
                    @DeprecatedInserts = Count(ct.[ID])
                FROM
                    inserted i
                INNER JOIN [dbo].[ContentTypes] ct
                    ON i.[ContentTypesID] = ct.[ID]
                WHERE
                    i.[ID] NOT IN (SELECT [ID] FROM deleted) -- UPDATEs are OK
                AND
                    ct.[Deprecated] = 1

                IF @DeprecatedInserts > 0
                BEGIN
                    RAISERROR('Cannot create deprecated site content.', 15, 1);
                    ROLLBACK TRANSACTION;
                    RETURN;
                END

                UPDATE sp
                    SET sp.[UpdateDate] = i.[UpdateDate]
                    FROM [dbo].[SitePages] sp
                    INNER JOIN [dbo].[PageContent] pc
                        ON sp.[ID] = pc.[SitePagesID]
                    INNER JOIN inserted i
                        ON pc.[SiteContentID] = i.[ID]
                
                IF @@TRANCOUNT > 0
                    COMMIT TRANSACTION;