mssql trigger needed

Question

mssql trigger needed

rajasekhar857

Valued Member

Points: 72
More actions
August 27, 2009 at 5:13 am

#133265

CREATE OR REPLACE TRIGGER TR_UPDATE_EMRMedicationsInfo
AFTER INSERT OR UPDATE OR DELETE ON EMRUNCODEDMEDICATIONSLKUP
FOR EACH ROW
DECLARE
EZEMRX_MED_ID NUMBER(20);
MED_DICTIONARY VARCHAR2(5);
ROWCNT NUMBER;
BEGIN
IF INSERTING THEN
MED_DICTIONARY := 'U';
ELSIF UPDATING THEN
MED_DICTIONARY := 'U';
ELSIF DELETING THEN
MED_DICTIONARY := 'U';
END IF;
SELECT EZEMRXID INTO EZEMRX_MED_ID FROM EMRIDS WHERE PROPERTY_NAME='EZEMRX_MED_ID';
EZEMRX_MED_ID := EZEMRX_MED_ID +1;
SELECT COUNT(EZEMRX_MED_ID) INTO ROWCNT FROM EMRCommonMedicationsLkup WHERE MEDICATIONS_ID = :new.UNCODED_MEDICATIONS_ID;
IF(ROWCNT > 0) THEN
DELETE FROM EMRCommonMedicationsLkup WHERE medications_id = :new.UNCODED_MEDICATIONS_ID;
END IF;
INSERT INTO EMRCommonMedicationsLkup (
EZEMRX_MED_ID, MEDICATIONS_ID, MEDICATION, DOSAGE, DISPENSE, GENERIC_NAME, STRENGTH,
COMPLETE_MED_NAME,MED_DICTIONARY, GROUP_ID, STATUS)
VALUES (
EZEMRX_MED_ID,:new.UNCODED_MEDICATIONS_ID, :new.MEDICATION, :new.DOSAGE, :new.DISPENSE, :new.GENERIC_NAME,
:new.STRENGTH, rtrim(ltrim(NVL(:new.MEDICATION,'') || ' ' || NVL(:new.STRENGTH,'') || ' ' || NVL(:new.DOSAGE,''))),
MED_DICTIONARY,:new.GROUP_ID, :new.STATUS );
UPDATE EMRIDS SET EZEMRXID = EZEMRX_MED_ID Where Property_Name='EZEMRX_MED_ID';
END;
/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This is not T-SQL code, I think it comes from Oracle and you're trying to port it in SQL Server.

You're not making any effort to do it by yourself and I don't think that posting your code here and waiting for somebody to it instead of you will help.

You have posted the original code: go on and try to make it on your own, post your T-SQL code here and ask for help when something is not clear.

If you just want somebody to port the trigger, hire a freelance DBA.

Regards

Gianluca

-- Gianluca Sartori

kramaswamy SSCoach Points: 18181 More actions · Answer 2

It is oracle, he's put that in the topic description. And yeah - do the work yourself, then post here if you can't get it to work 😛

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Please don't cross post. It just wastes peoples time and fragments replies.

No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic777774-145-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass