none
why does this trigger find rows when I can't find them?

    Question

  • I've got a trigger that seems to find rows in the INSERTED table when none exist. I can't figure out what's going on. I've got a foreign key defined on PARCEL that does cascading updates on the APPEALS table. When I update the PARCEL table, it updates the key in the APPEALS table and that fires the trigger and displays what seems to me to be a spurious error message from the trigger.

    Here's the definition of the trigger:

    CREATE TRIGGER [dbo].[Appeal_Ruling_Affirmed]
    ON [dbo].[Appeals]
    FOR INSERT, UPDATE
    AS
    DECLARE @counter int, @seq int, @yr as int, @id as varchar(5)

    IF EXISTS (select NULL from INSERTED where Ruling = 'AFFIRMED' AND TaxYear > 2008)
    BEGIN

    set @counter = (select count(*) from INSERTED where Ruling = 'AFFIRMED')
    set @seq = (select top 1 SeqNo from INSERTED where Ruling = 'AFFIRMED')
    set @id = (select top 1 LevelID from INSERTED where Ruling = 'AFFIRMED')
    set @yr = (select top 1 TaxYear from INSERTED where Ruling = 'AFFIRMED')
    PRINT 'Ruling = Affirmed, @counter = ' + convert(varchar(20), @counter) + ', LevelID = ' + @id +
    ', SeqNo = ' + convert(varchar(3), @seq) + ', TaxYear = ' + convert(char(4), @yr)

    When I run the update statement,

    UPDATE PARCEL set ParcelNo = '078 007.03' where ParcelNo = '078 007.03 000' and TaxYear = 2011

    the trigger fires and displays:

    Ruling = Affirmed, @counter = 1, LevelID = CB, SeqNo = 60, TaxYear = 2011

    When I check the data in the table, here's what I get:

    select TaxYear, LevelID, SeqNo, Ruling from Appeals where ParcelNo = '078 007.03 000' and TaxYear = 2011

    TaxYear LevelID SeqNo Ruling
    ------- ------- ----- ----------
    2011    AJ      100   REDUCED
    2011    CB      60    INCREASED

    So what's causing the trigger to fire? There aren't any rows in the table that's being modified that meet the criteria.

    Thanks for any ideas on troubleshooting this.


    Wednesday, July 24, 2013 2:01 PM

Answers

  • One of the other triggers was fired when the ParcelNo column changed. By mistake. With a lot of triggers on the table, it can be tricky figuring out what's causing the error. Jingyang Li's test was simple and that helped. So I've fixed the other trigger and things are working now. Thanks for the ideas, all.

    • Marked as answer by Gordon Prince Thursday, July 25, 2013 8:18 PM
    Thursday, July 25, 2013 8:09 PM

All replies

  • You filter on the old ParcelNo value  '078 007.03 000' ; what do you get when you use the updated value  '078 007.03'?:

    select TaxYear, LevelID, SeqNo, Ruling from Appeals 
    where ParcelNo = '078 007.03' and TaxYear = 2011


    Olaf Helper

    Blog Xing

    Wednesday, July 24, 2013 2:19 PM
  • No rows found.
    Wednesday, July 24, 2013 2:51 PM
  • Don't use variables in your trigger. You can use OUTPUT clause to export your results to another table. You can find example from this link:

    http://msdn.microsoft.com/en-us/library/ms177564.aspx

    Wednesday, July 24, 2013 2:58 PM
    Moderator
  • The code you posted does not compile. It contains a BEGIN but not an END. My guess, you didn't copy and paste the code and there is an END, but in the wrong place. But this is just a guess. Maybe you did copy and paste, but you didn't include all code.

    Example:

    CREATE TRIGGER [dbo].[Appeal_Ruling_Affirmed]
    ON [dbo].[Appeals]
    FOR INSERT, UPDATE
    AS
    DECLARE @counter int, @seq int, @yr as int, @id as varchar(5)
     
    IF EXISTS (select NULL from INSERTED where Ruling = 'AFFIRMED' AND TaxYear > 2008)
    BEGIN
       set @counter = (select count(*) from INSERTED where Ruling = 'AFFIRMED')
    END
    set @seq = (select top 1 SeqNo from INSERTED where Ruling = 'AFFIRMED')
    set @id = (select top 1 LevelID from INSERTED where Ruling = 'AFFIRMED')
    set @yr = (select top 1 TaxYear from INSERTED where Ruling = 'AFFIRMED')
    PRINT 'Ruling = Affirmed, @counter = ' + convert(varchar(20), @counter) + ', LevelID = ' + @id + ', SeqNo = ' + convert(varchar(3), @seq) + ', TaxYear = ' + convert(char(4), @yr)
    

    Wednesday, July 24, 2013 3:03 PM
  • Why do you use 'SELECT NULL FROM inserted' instead of 'SELECT 1 FROM inserted'? Logic comparing with NULL values or doing math with NULL values can give unexpected results. Also, the result can depend on settings like 'SET ANSI_NULLS ON|OFF' or 'SET CONCAT_NULL_YIELDS_NULL ON|OFF'. Try to avoid it if it is not necesarry.


    Wednesday, July 24, 2013 3:06 PM
  • Logic comparing with NULL values or doing math with NULL values can give unexpected results. .


    Chris, there is no compare on a NULL value. He uses the EXISTS function and that returns TRUE, if a row exist, doesn't matter what for values the row(s) have.

    Olaf Helper

    Blog Xing

    Wednesday, July 24, 2013 3:15 PM
  • In the trigger declaration there is one clause "FOR INSERT, UPDATE". Therefore when we run UPDATE command the trigger got fired. The output from the trigger is printed.

    Finally when we run SELECT statement the ParcelNo is not the updated one and even the trigger does not have DML language inside it.

    .


    Regards, RSingh



    • Edited by RSingh() Wednesday, July 24, 2013 3:51 PM txt change
    Wednesday, July 24, 2013 3:44 PM
  • A trigger fires if no row is updated.

    Add the following as the first line:

    IF not exists (select 1 from inserted ) return;


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Wednesday, July 24, 2013 3:57 PM
    Moderator
  • A trigger fires if no row is updated.

    I disagree; then the result + print out of the variable would return NULL, but they return a valid value <> NULL


    Olaf Helper

    Blog Xing

    Wednesday, July 24, 2013 4:09 PM
  • This code actually runs other logic in production (instead of setting the variables and then PRINTing them). What I posted just tests for any rows in INSERTED that meet the criteria. So this is a stripped down version just for troubleshooting.
    Wednesday, July 24, 2013 4:17 PM
  • Originally I had "SELECT 1" -- they both behave the same.

    Wednesday, July 24, 2013 4:18 PM
  • The rest of the trigger still fires with

    IF not exists (select 1 from inserted ) return;

    added.

    • Edited by Gordon Prince Wednesday, July 24, 2013 4:24 PM clarification
    Wednesday, July 24, 2013 4:23 PM
  • You are dealing with two tables from your question.

    Make sure you are working with the right trigger on the table

    UPDATE PARCEL .....

    CREATE TRIGGER [dbo].[Appeal_Ruling_Affirmed]
    ON [dbo].[Appeals]
    FOR INSERT, UPDATE
    AS ...

    select TaxYear, LevelID, SeqNo, Ruling from Appeals .....

    Wednesday, July 24, 2013 6:01 PM
    Moderator
  • I tried to make that clear in the original description of the issue. I'm updating the PARCEL table, which causes a cascading update of the APPEALS table, which fires the trigger that's causing the trouble.

    Wednesday, July 24, 2013 6:14 PM
  • If you post DDL of both tables, it may help to understand your issue. Thanks.
    Wednesday, July 24, 2013 6:37 PM
    Moderator
  • CREATE TABLE [dbo].[Parcel](
    [ParcelID] [int] IDENTITY(40000,1) NOT NULL,
    [ParcelNo] [varchar](35) NOT NULL,
    [TaxYear] [smallint] NOT NULL,
    [MatterNo] [varchar](21) NOT NULL,
    [Responsible] [varchar](4) NULL,
    [ParcelType] [char](1) NOT NULL
    [Timestamp] [timestamp] NULL,
    CONSTRAINT [Parcel_PK] PRIMARY KEY NONCLUSTERED
    (
    [ParcelNo] ASC,
    [TaxYear] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
    CONSTRAINT [IX_ParcelID] UNIQUE NONCLUSTERED
    (
    [ParcelID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    Wednesday, July 24, 2013 7:05 PM
  • CREATE TABLE [dbo].[Appeals](
    [ParcelNo] [varchar](35) NOT NULL,
    [TaxYear] [smallint] NOT NULL,
    [LevelID] [varchar](5) NOT NULL,
    [SeqNo] [tinyint] NOT NULL,
    [FiledBy] [char](1) NOT NULL,

    CONSTRAINT [Appeals_PK] PRIMARY KEY CLUSTERED
    (
    [ParcelNo] ASC,
    [TaxYear] ASC,
    [LevelID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Appeals]  WITH NOCHECK ADD  CONSTRAINT [FK_Appeals_Parcel] FOREIGN KEY([ParcelNo], [TaxYear])
    REFERENCES [dbo].[Parcel] ([ParcelNo], [TaxYear])
    ON UPDATE CASCADE
    GO

    Wednesday, July 24, 2013 7:07 PM
  • Thanks for providing DDL.

    I have added one coma in your table Parcel script and add one column to your table Appeals along with two rows of sample data.

    By using your existing trigger code, the test is working fine. and it returns the updated record as expected.

    Wednesday, July 24, 2013 7:32 PM
    Moderator
  • I reduced what I sent you as the DDL statements, as there are many more columns in each table and several other triggers in each table.

    Please try adding the column Appeals.Ruling, then try your test again with Appeals.Ruling = 'AFFIRMED' in one of the rows and then with none of the rows having Appeals.Ruling = 'AFFIRMED' .

    Thanks.

    Wednesday, July 24, 2013 7:41 PM
  • There are 20 other triggers on the APPEALS table. I disabled them all and now the update statement runs. I think I have found one of the triggers that is updating APPEALS data, which is causing the otherwise unexplained message that I started with. I am going to troubleshoot this further on my end. Thanks everyone for your ideas.
    Thursday, July 25, 2013 2:24 PM
  • One of the other triggers was fired when the ParcelNo column changed. By mistake. With a lot of triggers on the table, it can be tricky figuring out what's causing the error. Jingyang Li's test was simple and that helped. So I've fixed the other trigger and things are working now. Thanks for the ideas, all.

    • Marked as answer by Gordon Prince Thursday, July 25, 2013 8:18 PM
    Thursday, July 25, 2013 8:09 PM
  • Please mark you last reply as answer so that other forum members know this problem is solved.

    Thank you

    Thursday, July 25, 2013 8:16 PM