none
trigger update

    Question

  • i create this trigger to stop any insert or update in case tdt_dat<'2018/04/01' and tdt_typ 

    it's run ok in "insert" but in "update" i get this message

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    -- this is trigger

     CREATE TRIGGER [dbo].[trg_tst]

     ON [dbo].[AccTransDet]
     INSTEAD OF INSERT,UPDATE,DELETE
     AS
     BEGIN
     If (SELECT tdt_dat FROM inserted) <'2018/04/01' and (SELECT tdt_typ FROM inserted) <>0
          Begin   
     RAISERROR ('Sorry You Cannot Do Any Action ... ',16,1)
         End
     END
    GO

    Dev M.Basha

    Sunday, April 29, 2018 2:29 PM

Answers

  • This is one of the common misconception we come across in trigger development. Keep in mind trigger doesnt work on row by row concept so for batch DML operations , the internal temporary tables will have more than one row so you cant just compare select resultset to a single value as you did. You need to use EXISTS condition to check for the presence of any exceptions and raise error accordingly

    hence you need to write the logic as below for handling those cases

    CREATE TRIGGER [dbo].[trg_tst]
     ON [dbo].[AccTransDet]
     INSTEAD OF INSERT,UPDATE,DELETE
     AS
     BEGIN
     If EXISTS (
    SELECT 1 FROM inserted 
    WHERE tdt_dat <'2018/04/01' 
    and tdt_typ  <>0
    )
          Begin    
     RAISERROR ('Sorry You Cannot Do Any Action ... ',16,1)
         End
     END
    GO


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Sunday, April 29, 2018 5:08 PM
    • Marked as answer by dev.basha Monday, April 30, 2018 7:10 AM
    Sunday, April 29, 2018 5:07 PM

All replies

  • Hi Dev,

    Triggers work on sets of data, and not row by row. Looks like query is returning more than one result in the inserted operation. 

    Thanks,

    Alambir

    Please vote this as helpful or mark this as answered if this helps.


    Sunday, April 29, 2018 4:19 PM
  • This is one of the common misconception we come across in trigger development. Keep in mind trigger doesnt work on row by row concept so for batch DML operations , the internal temporary tables will have more than one row so you cant just compare select resultset to a single value as you did. You need to use EXISTS condition to check for the presence of any exceptions and raise error accordingly

    hence you need to write the logic as below for handling those cases

    CREATE TRIGGER [dbo].[trg_tst]
     ON [dbo].[AccTransDet]
     INSTEAD OF INSERT,UPDATE,DELETE
     AS
     BEGIN
     If EXISTS (
    SELECT 1 FROM inserted 
    WHERE tdt_dat <'2018/04/01' 
    and tdt_typ  <>0
    )
          Begin    
     RAISERROR ('Sorry You Cannot Do Any Action ... ',16,1)
         End
     END
    GO


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Sunday, April 29, 2018 5:08 PM
    • Marked as answer by dev.basha Monday, April 30, 2018 7:10 AM
    Sunday, April 29, 2018 5:07 PM
  • sorry ... it's run ok in insert case "tdt_dat <'2018/04/01'
    and tdt_typ  <>0 " and i message show

    but if tdt_dat >'2018/04/01'

    don't insert or do any action


    Dev M.Basha

    Monday, April 30, 2018 9:31 AM
  • sorry ... it's run ok in insert case "tdt_dat <'2018/04/01'
    and tdt_typ  <>0 " and i message show

    but if tdt_dat >'2018/04/01'

    don't insert or do any action


    Dev M.Basha

    do you mean this then?

    CREATE TRIGGER [dbo].[trg_tst]
     ON [dbo].[AccTransDet]
     INSTEAD OF INSERT,UPDATE,DELETE
     AS
     BEGIN
     If EXISTS (
    SELECT 1 FROM inserted 
    WHERE tdt_dat <'2018/04/01' 
    and tdt_typ  <>0
    )
    BEGIN
    INSERT INTO [dbo].[AccTransDet]
    SELECT * FROM INSERTED

    SELECT 'Records Inserted!!'
    END
    ELSE Begin RAISERROR ('Sorry You Cannot Do Any Action ... ',16,1) End END GO


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, April 30, 2018 9:38 AM
  • oooook .....  thank you 

    i do this with 

    insert with trigger

    and update with another trigger


    Dev M.Basha

    Monday, April 30, 2018 11:10 AM
  • oooook .....  thank you 

    i do this with 

    insert with trigger

    and update with another trigger


    Dev M.Basha

    nope

    you can do both inside same trigger

    like this

    CREATE TRIGGER [dbo].[trg_tst]
     ON [dbo].[AccTransDet]
     INSTEAD OF INSERT,UPDATE,DELETE
     AS
     BEGIN
     If EXISTS (
    SELECT 1 FROM inserted 
    WHERE tdt_dat <'2018/04/01' 
    and tdt_typ  <>0
    )
    BEGIN
    INSERT INTO [dbo].[AccTransDet]
    SELECT * FROM INSERTED i
    WHERE NOT EXISTS
    (
    SELECT 1
    FROM DELETED
    WHERE PK = i.PK
    )
    
    UPDATE t
    SET Column1 = i.Column1,
    Column2 = i.Column2,
    ..
    FROM [dbo].[AccTransDet] t
    JOIN INSERTED i
    ON i.PK = t.PK
    JOIN DELETED d
    ON d.PK = t.PK
    
    
    SELECT 'Records Inserted!!'
    END
    ELSE If EXISTS (
    SELECT 1 FROM deleted
    WHERE tdt_dat <'2018/04/01' 
    and tdt_typ  <>0
    )
    BEGIN
    DELETE t
    FROM [dbo].[AccTransDet] t
    JOIN DELETED d
    ON d.PK = t.PK
    WHERE NOT EXISTS 
    (
    SELECT 1
    FROM INSERTED
    WHERE PK = t.PK
    )
    END
    ELSE
          Begin    
     RAISERROR ('Sorry You Cannot Do Any Action ... ',16,1)
         End
     END
    GO

    PK is the primary key of your table

    Column1, COlumn2 etc are columns in table to be updated with new values


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, April 30, 2018 11:18 AM