CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012database-trigger
BozkurtFuat
BozkurtFuat

Reputation: 13

MSSQL Trigger for update is working only once

I'm going to create a trigger for update. Purpose of this trigger is that If muadurum column is changed , take the old value of mua_tarih in table fkayitlar and insert to another table mua_tarihleri.

My code block like;

ALTER TRIGGER [dbo].[trgr_fkayit_update]
ON [dbo].[fkayitlar]
AFTER UPDATE
AS
DECLARE @mua_durum_once NVARCHAR(10)
DECLARE @mua_durum_sonra NVARCHAR(10)
DECLARE @mua_tarih_once DATE
DECLARE @mua_yapan_once NVARCHAR(25)
DECLARE @kisi_id INT

Take the old value;

SELECT
@kisi_id=kayitid,
@mua_durum_once=muayenedurum,
@mua_tarih_once=muayenetarih,
@mua_yapan_once=mua_yapan 
FROM deleted

Take the new value;

SELECT @mua_durum_sonra=muayenedurum FROM inserted

Check if value is changed ; if changed, Insert @mua_tarih to table mua_tarihleri with @kisi_id and @mua_yapan_once

  IF @mua_durum_once='OLDU' 
  AND @mua_durum_sonra='OLMADI' 
   AND @mua_tarih_once IS NOT NULL
   BEGIN
    INSERT INTO mua_tarihleri(kayitid,mua_tarihi,mua_yapan) 
        VALUES(@kisi_id,@mua_tarih_once,@mua_yapan_once)
     END

My problem is When I update more than one row in table fkayitlar,Trigger is working, but I see only one inserted row in table mua_tarihleri (only working once). I need to see more than one.(should be working more than once) Are not Triggers working on more than one process? or How can I solve this my problem?

Upvotes: 1

Views: 1011

Answers (1)

Ɖiamond ǤeezeƦ
Ɖiamond ǤeezeƦ

Reputation: 3331

The trigger only occurs once when the table is updated, no matter how many rows are updated. Therefore, you have to write your trigger body to operate on a set of rows, not a single row as you have done.

Should be something like:

ALTER TRIGGER [dbo].[trgr_fkayit_update]
ON [dbo].[fkayitlar]
AFTER UPDATE
AS
    INSERT INTO mua_tarihleri(kayitid,mua_tarihi,mua_yapan) 
    SELECT deleted.kayitid, deleted.muayenedurum, deleted.muayenetarih, deleted.mua_yapan 
    FROM deleted
    JOIN inserted ON deleted.kayitid = inserted.kayitid
    WHERE deleted.muayenedurum='OLDU'
        AND inserted.muayenedurum='OLMADI'
        AND muayenetarih IS NOT NULL

Upvotes: 1

Related Questions