0

I have a SQL Server trigger that I have tried to convert for Firebird but seems like I have some trouble with it.

Here is my SQL trigger:

CREATE TRIGGER [dbo].[IDSOFT_INTERV_UI]
   ON [MMAINT_SOCO_PROD].[dbo].[T_ZONES_LIBRES_UI]
   AFTER UPDATE
AS 
BEGIN
    IF UPDATE(STR_ZONE_LIBRE)
        BEGIN

        DECLARE @ui_id int,
                @num_interv int;
                
        SELECT @ui_id = d.LIEN_UI,
               @num_interv = e.NUM_INTERV
        FROM T_LIGNE_BT a
        inner join T_BT b on b.NUM_BT = a.CLE_BT
        inner join T_UI c on c.NUM_UI = b.CLE_UI
        inner join T_ZONES_LIBRES_UI d on d.LIEN_UI = c.NUM_UI
        inner join T_INTERV e on e.NOM_INTERV = d.STR_ZONE_LIBRE
        WHERE d.LIEN_UI in (select LIEN_UI from INSERTED) and d.INDICE_ZONE_LIBRE = 20;     
        
    Update a
    set a.cle_element = e.NUM_INTERV
    from t_ligne_bt a
    inner join T_BT b on b.NUM_BT = a.CLE_BT
    inner join T_UI c on NUM_UI = b.CLE_UI
    inner join T_ZONES_LIBRES_UI d on d.LIEN_UI = c.NUM_UI
    inner join T_INTERV e on e.NOM_INTERV = d.STR_ZONE_LIBRE
    where d.LIEN_UI = @ui_id
        and b.cle_ip is not null 
        and b.cle_ip <> 100000
        and a.type_ligne_bt like 'I'

      END
END

I'm really having problems to replicate the same for Firebird.

Can anyone help me please.

Charly Thomas
  • 1
  • Have you checked the documentation? – Dale K Feb 22 '21 at 06:09
  • Please explain what this trigger is supposed to do, and describe what problems you're having to replicate it. One important thing to keep in mind is that Firebird triggers fire per **row** (with the changes of a single row in the record variable `NEW` and previous values in `OLD`), while SQL Server triggers fire per **statement** (with the changes for all affected rows in pseudo-tables `INSERTED` and previous values in `DELETED`). – Mark Rotteveel Feb 22 '21 at 07:32
  • When I update d.STR_ZONE_LIBRE, for a specific row, I want a.CLE_ELEMENT to be updated. In d.STR_ZONE_LIBRE it's gonna be a nvarchar value (a name to be exact), then i want to match this field with the value in e.NOM_INTERV to return the key (e.NUM_INTERV) which will end up in a.CLE_ELEMENT. I have a lot of inner join for the relation between table a and table d. I can't figure out how to write an update script in firebird with multiple inner join. I tried with "exists" but can't make it work... I really don't know a lot about firebird, i'm sorry. – Charly Thomas Feb 23 '21 at 04:26
  • I find this one : MERGE INTO T_LIGNE_BT as a USING T_BT as b ON b.NUM_BT = a.CLE_BT and b.num_bt in (77, 84, 147) WHEN MATCHED THEN update set a.cle_element = xxx How can i add more tables ? – Charly Thomas Feb 23 '21 at 05:21
  • Please look at the documentation of [`MERGE`](https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-dml-merge). You can use a select statement as the source. – Mark Rotteveel Feb 23 '21 at 09:05

0 Answers0