August 26, 2009 at 1:01 pm
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;
/
hi please
give me the equivalent mssql trigger as iam very new to mssql and this is my first post
August 27, 2009 at 5:07 am
can anyone help me in giving mssql trigger as iam facing so much difficulty in it
August 27, 2009 at 7:53 am
Okay, I'm looking at it, but while I'm looking at it I suggest you read this article[/url] as triggers perform differently in SQL Server than in Oracle.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2009 at 8:22 am
I think I understand what the trigger is supposed to do (not an ORACLE guy) so I think this is how I would do it in SQL Server. Definitely test it in a dev environment first!
A couple of notes:
1. I removed the DELETE part as your code was doing nothing if it was a delete since it is an AFTER trigger.
2. I didn't do the rowcount as joining to the Virtual inserted table will do the delete if there are any rows. Plus because SQL Server triggers need to work with multiple rows you would have problem if more than 1 id is inserted or updated. You could do an IF EXISTS, but that is really unnecessary.
Feel free to post any questions you may have on this thread.
CREATE TRIGGER TR_UPDATE_EMRMedicationsInfo ON EMRUNCODEDMEDICATIONSLKUP
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
DECLARE @EZEMRX_MED_ID NUMERIC(20),
@MED_DICTIONARY VARCHAR(5)
SET @MED_DICTIONARY = 'U'
/* Get the most recent ID Number */
SELECT
@EZEMRX_MED_ID = EZEMRXID
FROM
EMRIDS
WHERE
PROPERTY_NAME = 'ESMERX_MED_ID';
/* add 1 to it */
SET @EZEMRX_MED_ID = @EZEMRX_MED_ID + 1;
/*
Deletes any existing rows in the lookup table that
are part of the insert, update, or delete
*/
DELETE CML
FROM
EMRCommonMedicationsLkup AS CML JOIN
inserted AS I ON
CML.MEDICATIONS_ID = I.UNCODED_MEDICATIONS_ID;
/*
Inserts the new row if it is an insert or update
*/
INSERT INTO EMRCommonMedicationsLkup
(
EZEMRX_MED_ID,
MEDICATIONS_ID,
MEDICATION,
DOSAGE,
DISPENSE,
GENERIC_NAME,
STRENGTH,
COMPLETE_MED_NAME,
MED_DICTIONARY,
GROUP_ID,
STATUS
)
SELECT
@EZEMRX_MED_ID,
I.UNCODED_MEDICATIONS_ID,
I.MEDICATION,
I.DOSAGE,
I.DISPENSE,
I.GENERIC_NAME,
I.STRENGTH,
RTRIM(LTRIM(ISNULL(I.MEDICATION,'') + ' ' + ISNULL(I.STRENGTH, '') + ' ' + ISNULL(I.DOSAGE, ''))),
@MED_DICTIONARY,
I.GROUP_ID,
I.STATUS
FROM
inserted AS I
/*
Set the id value in the id table
*/
UPDATE EMRIDS
SET EZEMRXID = @EZEMRX_MED_ID
WHERE
PROPERTY_NAME = 'EXEMRX_MED_ID'
Oh and in order to change an object in SQL Server you need to change the CREATE to ALTER. I wish SQL Server had the CREATE OR REPLACE syntax.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2009 at 9:29 am
thanks jack
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply