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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

oooook .....  thank you 

i do this with 

insert with trigger

and update with another trigger

---

Dev M.Basha

Answer 6

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