Answered by:
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.
- Edited by Alambir Sandhu Sunday, April 29, 2018 5:29 PM
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 showbut 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 showbut 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 PageMonday, 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 PageMonday, April 30, 2018 11:18 AM