CREATE TRIGGER TR_DELETE_EMRTransactions ON EMRTransactions FOR DELETE  
AS   
BEGIN  
DECLARE 
      @CO_TRANSACTION_ID VARCHAR(50)
          select  @CO_TRANSACTION_ID = COALESCE(@CO_TRANSACTION_ID + ',', '')  + CAST
                (EMR_TRANSACTION_ID AS VARCHAR(20)) FROM DELETED WHERE ROLE_NAME IN ('CONSULTANT')
      if(@CO_TRANSACTION_ID!=null AND @CO_TRANSACTION_ID!='')
            set @CO_TRANSACTION_ID = substring(@CO_TRANSACTION_ID,0,len(@CO_TRANSACTION_ID))
            print @CO_TRANSACTION_ID
            DELETE FROM EMRCOTransactions WHERE CAST(EMR_TRANSACTION_ID AS VARCHAR(50)) IN (@CO_TRANSACTION_ID)
            --select * from EMRCOTransactions WHERE CAST(EMR_TRANSACTION_ID AS VARCHAR(50)) IN (@CO_TRANSACTION_ID)
END
GO

CREATE TRIGGER TR_DELETE_EMRTransactions ON EMRTransactions FOR DELETE  
AS   
BEGIN  
      
            DELETE e
            FROM EMRCOTransactions e
            JOIN DELETED d
            ON CAST(e.EMR_TRANSACTION_ID AS VARCHAR(50))=  CAST(d.EMR_TRANSACTION_ID AS VARCHAR(20))
            WHERE d.ROLE_NAME IN ('CONSULTANT')
END
GO

quote:

---

Originally posted by madhivanan

No need of CAST as both would have the same datatype

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by rajasekhar857

Hi visakh and Madhi sorry to ask once agin as Trigger given by you is working extremely good.
But if i try in oracle the same with this what need to be modified.

i have written delete query like
DELETE FROM EMRCOTransactions e JOIN DELETED d ON (e.EMR_TRANSACTION_ID)= d.EMR_TRANSACTION_ID) WHERE d.ROLE_NAME IN ('CONSULTANT')
what needs to replace Deleted as it is OLD.EMRTransactions OR EMRTransactions.help me in this regard too

---

quote:

---

Originally posted by rajasekhar857

Thanks Visakh and Madhi once again

---