title: SQL Server : delete trigger not working
body: <p>Below is table <code>Country</code>:</p>
  
  <pre><code>+------------------+-----------+
  |   COLUMN_NAME    | DATA_TYPE |
  +------------------+-----------+
  | ID               | int       |
  | Name             | varchar   |
  | CapitalCity      | varchar   |
  | Population       | int       |
  | OfficialReligion | varchar   |
  | OfficialLanguage | varchar   |
  | DateEntered      | datetime  |
  +------------------+-----------+
  </code></pre>
  
  <p>I then have another very similar table called <code>CountryRecord</code> which has the same columns and then two additional new ones:</p>
  
  <pre><code>+-------------------------+-----------+
  |   COLUMN_NAME           | DATA_TYPE |
  +-------------------------+-----------+
  | ID                      | int       |
  | OriginalID              | int       |
  | Name                    | varchar   |
  | CapitalCity             | varchar   |
  | Population              | int       |
  | OfficialReligion        | varchar   |
  | OfficialLanguage        | varchar   |
  | DateEntered             | datetime  |
  | QueryType               | varchar   |
  +-------------------------+-----------+
  </code></pre>
  
  <p>When inserting, updating or deleting data in <code>Country</code>, a trigger is called that will also insert rows into <code>CountryRecord</code> for the purpose of recording what query was made for what row of data. The trigger is as follows:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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 <code>CountryRecord</code> after running the delete query shows it hasn't increased by one).</p>
  
  <p><strong>Update:</strong> Changing the delete trigger to use <code>ELSE</code> 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.</p>
  
  <p><strong>Update 2:</strong> Just to check that I was only affecting one row, I added in select queries for each trigger as well as a print of <code>@CountryID</code> - the select queries showed only 1 row, while <code>@CountryID</code> 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.</p>
  
  <p><strong>Update 3:</strong> 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:</p>
  
  <pre><code>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;
  </code></pre>