SQL Server : delete trigger not working

Question

Below is table Country:

+------------------+-----------+
|   COLUMN_NAME    | DATA_TYPE |
+------------------+-----------+
| ID               | int       |
| Name             | varchar   |
| CapitalCity      | varchar   |
| Population       | int       |
| OfficialReligion | varchar   |
| OfficialLanguage | varchar   |
| DateEntered      | datetime  |
+------------------+-----------+

I then have another very similar table called CountryRecord which has the same columns and then two additional new ones:

+-------------------------+-----------+
|   COLUMN_NAME           | DATA_TYPE |
+-------------------------+-----------+
| ID                      | int       |
| OriginalID              | int       |
| Name                    | varchar   |
| CapitalCity             | varchar   |
| Population              | int       |
| OfficialReligion        | varchar   |
| OfficialLanguage        | varchar   |
| DateEntered             | datetime  |
| QueryType               | varchar   |
+-------------------------+-----------+

When inserting, updating or deleting data in Country, a trigger is called that will also insert rows into CountryRecord for the purpose of recording what query was made for what row of data. The trigger is as follows:

ALTER TRIGGER [dbo].[CountryRecord_Trigger]
ON [dbo].[Country]
AFTER UPDATE, INSERT, DELETE
AS
    DECLARE @CountryID INT, @queryType VARCHAR(20);

    --Update trigger
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        SET @queryType = 'UPDATE';

        SELECT @CountryID = ID FROM inserted i;

        INSERT INTO CountryRecord (OriginalID, Name, CapitalCity, Population, OfficialReligion, OfficialLanguage, DateEntered, QueryType) 
            SELECT  
                ID, Name, CapitalCity, Population, OfficialReligion, 
                OfficialLanguage, DateEntered, @queryType 
            FROM
                Country 
            WHERE
                @CountryID = ID;
    END

    --Insert trigger
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)       
    BEGIN
        SET @queryType = 'INSERT';

        SELECT @CountryID = ID FROM inserted i;

        INSERT INTO CountryRecord (OriginalID, Name, CapitalCity, Population, OfficialReligion, OfficialLanguage, DateEntered, QueryType) 
            SELECT 
                ID, Name, CapitalCity, Population, OfficialReligion,
                OfficialLanguage, DateEntered, @queryType 
            FROM
                Country 
            WHERE
                @CountryID = ID;
     END

     --Delete trigger
     IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
     BEGIN
         SET @queryType = 'DELETE';

         SELECT @CountryID = ID FROM deleted d;

         INSERT INTO CountryRecord (OriginalID, Name, CapitalCity, Population, OfficialReligion, OfficialLanguage, DateEntered, QueryType) 
             SELECT 
                 ID, Name, CapitalCity, Population, OfficialReligion,
                 OfficialLanguage, DateEntered, @queryType 
             FROM
                 Country 
             WHERE
                 @CountryID = ID;
     END

However, while the Insert and Update triggers work, the Delete trigger does not. When I run a delete query, I pick only one row, so I'm fairly sure that the delete query is not selecting more than one row (instead, seems like it's selecting nothing, since doing a check of the current identity of CountryRecord after running the delete query shows it hasn't increased by one).

Update: Changing the delete trigger to use ELSE instead of meeting the condition set does not fix the issue. Instead, any update queries will call both the update and delete trigger, while delete queries still have no effect.

Update 2: Just to check that I was only affecting one row, I added in select queries for each trigger as well as a print of @CountryID - the select queries showed only 1 row, while @CountryID did not come up as null/blank. I also tried altering the columns thinking if the NOT NULL constraint had any effect, but it didn't.

Update 3: I have got Delete working now, although it only works for single row deletes. Multi row deletes do not work properly - if for example I delete two records, only one row goes into the audit table. I have also updated Insert and Update to use a similar code structure, so they also have the same multi row issue. Code snippet for the delete portion:

SET @queryType = 'DELETE';
SELECT CountryID = ID FROM deleted;
INSERT INTO CountryRecord (OriginalID, Name, CapitalCity, Population, OfficialReligion, OfficialLanguage, DateEntered, QueryType) 
    SELECT ID, Name, CapitalCity, Population, OfficialReligion, OfficialLanguage, DateEntered, @queryType FROM deleted WHERE @CountryID = ID;
DECLARE @auditID int;
SET @auditID = SCOPE_IDENTITY();
UPDATE CountryRecord SET QueryType = @queryType WHERE ID = @auditID;

Answer 1

The reason your delete doesn't work is because the record has already been deleted from the country table. So you can't select it out of there.

You need to get it from the deleted table instead.

Your other two queries also refer to the Country tables in a trigger which is a bad idea. You shouldn't use the source table in your query - you should use inserted and deleted instead.

Have a think about how you could write one query from the inserted and deleted tables that does everything that you want, in one query, no matter how many records are in inserted and deleted. If you don't abandon this question (it happens a lot), I will add an example to this answer.

Here is a query that saves your inserts and updates in one go:

INSERT INTO CountryRecord (
 Name, 
 CapitalCity, 
 Population, 
 OfficialReligion, 
 OfficialLanguage, 
 DateEntered, 
 QueryType)
SELECT 
 I.Name, 
 I.CapitalCity, 
 I.Population, 
 I.OfficialReligion, 
 I.OfficialLanguage,
 GETDATE() As DateEntered, 
 CASE 
   WHEN EXISTS(SELECT * FROM DELETED D WHERE D.ID = I.ID) 
   THEN 'U' ELSE 'I' 
 END As QueryType
 FROM INSERTED I

I have left out OriginalID because people you should not be updating the primary key and besides, its impossible to work out what was updated in this case. You can only do that in a BEFORE trigger

Here's one that captures your deletes. I've done these separately so they are easier to understand, and will probably perform better for large recordsets

INSERT INTO CountryRecord (
 Name, 
 CapitalCity, 
 Population, 
 OfficialReligion, 
 OfficialLanguage, 
 DateEntered, 
 QueryType)
SELECT 
 D.Name, 
 D.CapitalCity, 
 D.Population, 
 D.OfficialReligion, 
 D.OfficialLanguage,
 GETDATE() As DateEntered, 
 'D' As QueryType
 FROM DELETED D
 WHERE NOT EXISTS (SELECT * FROM INSERTED I WHERE I.ID = D.ID)

These will work regardless of how many rows have been altered

If you want to actually check which columns changed in an update, you can again compare them individually in the select statement, or you can use the UPDATED() function

Is this a school question? Because triggers are really a last resort, but people insist on teaching them in school still even though they are mostly irrelevant.

What is relevant is the set based solution I have posted here