trigger required in mssql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

trigger required in mssql

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-26 : 10:00:59

hi,please give me this this oracle trigger into mssql.



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;
  /

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-26 : 13:56:40

Have you looked in books online for triggers? Is your issue with trigger syntax or T-SQL syntax in general?

An infinite universe is the ultimate cartesian product.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-26 : 14:01:38

I have did like this but iam not sure


CREATE TRIGGER TR_UPDATE_EMRMedicationsInfo 
    ON dbo.EMRUNCODEDMEDICATIONSLKUP 
    FOR INSERT, UPDATE, DELETE 
AS
DECLARE
      @EZEMRX_MED_ID  NUMERIC(20),
      @MED_DICTIONARY VARCHAR(5),
      @ROWCNT         NUMERIC
    IF (SELECT COUNT(*) FROM inserted) > 0 
    BEGIN 
        IF (SELECT COUNT(*) FROM deleted) > 0 
	 SELECT @EZEMRX_MED_ID= EZEMRXID FROM EMRIDS WHERE PROPERTY_NAME='EZEMRX_MED_ID'
              @EZEMRX_MED_ID = @EZEMRX_MED_ID +1
           SELECT @ROWCNT=COUNT(EZEMRX_MED_ID) FROM EMRCommonMedicationsLkup WHERE MEDICATIONS_ID =UNCODED_MEDICATIONS_ID

	  IF(ROWCNT > 0) 
	       DELETE FROM EMRCommonMedicationsLkup WHERE medications_id = UNCODED_MEDICATIONS_ID
          END
        BEGIN 
            -- update! 
            INSERT EMRCommonMedicationsLkup (EZEMRX_MED_ID, MEDICATIONS_ID, MEDICATION, DOSAGE, DISPENSE, GENERIC_NAME, STRENGTH, 
        COMPLETE_MED_NAME,MED_DICTIONARY, GROUP_ID, STATUS)
            SELECT EZEMRX_MED_ID, MEDICATIONS_ID, MEDICATION, DOSAGE, DISPENSE, GENERIC_NAME, STRENGTH, 
        COMPLETE_MED_NAME,MED_DICTIONARY, GROUP_ID, STATUS FROM inserted
        END 
        ELSE 
        BEGIN 
            -- insert! 
           INSERT EMRCommonMedicationsLkup (EZEMRX_MED_ID, MEDICATIONS_ID, MEDICATION, DOSAGE, DISPENSE, GENERIC_NAME, STRENGTH, 
        COMPLETE_MED_NAME,MED_DICTIONARY, GROUP_ID, STATUS)
            SELECT EZEMRX_MED_ID, MEDICATIONS_ID, MEDICATION, DOSAGE, DISPENSE, GENERIC_NAME, STRENGTH, 
        COMPLETE_MED_NAME,MED_DICTIONARY, GROUP_ID, STATUS FROM inserted
        END 
    END 
    ELSE 
    BEGIN 
        -- delete! 
         INSERT EMRCommonMedicationsLkup (EZEMRX_MED_ID, MEDICATIONS_ID, MEDICATION, DOSAGE, DISPENSE, GENERIC_NAME, STRENGTH, 
        COMPLETE_MED_NAME,MED_DICTIONARY, GROUP_ID, STATUS)
            SELECT EZEMRX_MED_ID, MEDICATIONS_ID, MEDICATION, DOSAGE, DISPENSE, GENERIC_NAME, STRENGTH, 
        COMPLETE_MED_NAME,MED_DICTIONARY, GROUP_ID, STATUS FROM deleted
    END
GO

can u please help me in making out into mssql trigger

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-26 : 14:30:54

Your update and inserted sections are identical. So does this mean you're inserting into EMRCommonMedicationsLkup in every case? If that is so then you can get rid of the first nested if statement.

like so:


IF (SELECT COUNT(*) FROM inserted) > 0 
    BEGIN 
        -- insert! and   -- update! 
       INSERT EMRCommonMedicationsLkup (EZEMRX_MED_ID, MEDICATIONS_ID, MEDICATION, DOSAGE, DISPENSE, GENERIC_NAME, STRENGTH, 
      COMPLETE_MED_NAME,MED_DICTIONARY, GROUP_ID, STATUS)
        SELECT EZEMRX_MED_ID, MEDICATIONS_ID, MEDICATION, DOSAGE, DISPENSE, GENERIC_NAME, STRENGTH, 
     COMPLETE_MED_NAME,MED_DICTIONARY, GROUP_ID, STATUS FROM inserted
    END 
    ELSE 
    BEGIN 
        -- delete! 
         INSERT EMRCommonMedicationsLkup (EZEMRX_MED_ID, MEDICATIONS_ID, MEDICATION, DOSAGE, DISPENSE, GENERIC_NAME, STRENGTH, 
        COMPLETE_MED_NAME,MED_DICTIONARY, GROUP_ID, STATUS)
            SELECT EZEMRX_MED_ID, MEDICATIONS_ID, MEDICATION, DOSAGE, DISPENSE, GENERIC_NAME, STRENGTH, 
        COMPLETE_MED_NAME,MED_DICTIONARY, GROUP_ID, STATUS FROM deleted
    END

Understand?

An infinite universe is the ultimate cartesian product.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-26 : 14:38:23

when ever i inserts value shold be inserted and when i update old record should got deleted and updated one should be there.on awhole please check my code and send me the corrected ones.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-26 : 14:40:56

So you want the corresponding record in EMRCommonMedicationsLkup to be updated, inserted or deleted based on the same action in EMRUNCODEDMEDICATIONSLKUP ?

An infinite universe is the ultimate cartesian product.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-26 : 14:41:03

please tell me how to write this particular portion of block in sql server

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;

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-26 : 14:41:29

yes exactly

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-26 : 14:44:55

What column(or columns) are the key?

An infinite universe is the ultimate cartesian product.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-26 : 14:46:58

quote:
Originally posted by rajasekhar857

please tell me how to write this particular portion of block in sql server

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;

Well I'm no Oracle expert but it seems like this piece of code


 IF INSERTING THEN
             MED_DICTIONARY := 'U';
        ELSIF UPDATING THEN
             MED_DICTIONARY := 'U';
        ELSIF DELETING THEN
             MED_DICTIONARY := 'U';
       END IF;

Could have been reduced to this:

MED_DICTIONARY := 'U';

An infinite universe is the ultimate cartesian product.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-26 : 14:46:58

DISPENSE, GENERIC_NAME, STRENGTH

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-27 : 05:20:58

CREATE TRIGGER TR_UPDATE_EMRMedicationsInfo
ON dbo.EMRUNCODEDMEDICATIONSLKUP
FOR INSERT, UPDATE, DELETE
AS
DECLARE
@EZEMRX_MED_ID NUMERIC(20),
@MED_DICTIONARY VARCHAR(5),
@ROWCNT NUMERIC,
@MEDICATIONS_ID NUMERIC(20),
@MEDICATION VARCHAR(255),
@DOSAGE VARCHAR(255),
@DISPENSE VARCHAR(255) ,
@GENERIC_NAME VARCHAR(255),
@STRENGTH VARCHAR(255) ,
@COMPLETE_MED_NAME VARCHAR(400),
@GROUP_ID NUMERIC(20),
@STATUS VARCHAR(2),
@UNCODED_MEDICATIONS_ID NUMERIC(20)

BEGIN
IF (SELECT COUNT(*) FROM deleted) > 0
SELECT @EZEMRX_MED_ID= EZEMRXID FROM EMRIDS WHERE PROPERTY_NAME='EZEMRX_MED_ID'
SET @EZEMRX_MED_ID = @EZEMRX_MED_ID +1
SELECT @ROWCNT=COUNT(@EZEMRX_MED_ID) FROM EMRCommonMedicationsLkup WHERE MEDICATIONS_ID =@UNCODED_MEDICATIONS_ID

IF(@ROWCNT > 0)
DELETE FROM EMRCommonMedicationsLkup WHERE medications_id = @UNCODED_MEDICATIONS_ID
END
BEGIN

IF (SELECT COUNT(*) FROM inserted) > 0
BEGIN
-- insert and update!
INSERT EMRCommonMedicationsLkup
SELECT @EZEMRX_MED_ID, @MEDICATIONS_ID, @MEDICATION, @DOSAGE, @DISPENSE, @GENERIC_NAME, @STRENGTH,
@COMPLETE_MED_NAME,@MED_DICTIONARY, @GROUP_ID, @STATUS FROM inserted
END

ELSE

BEGIN
-- delete!
INSERT EMRCommonMedicationsLkup
SELECT @EZEMRX_MED_ID, @MEDICATIONS_ID, @MEDICATION, @DOSAGE, @DISPENSE, @GENERIC_NAME, @STRENGTH,
@COMPLETE_MED_NAME,@MED_DICTIONARY, @GROUP_ID, @STATUS FROM deleted
UPDATE EMRIDS SET EZEMRXID = @EZEMRX_MED_ID Where Property_Name='EZEMRX_MED_ID';

END
END
GO

please give me the corrected ones

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources