trigger of oracle to 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 of oracle to mssql

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-04-02 : 07:33:16

Hi,
can anyone help me how to use tis oracle trigger in mssql

CREATE OR REPLACE TRIGGER TR_Update_EMRPatientProblems AFTER UPDATE OF ENCOUNTER_ID ON EMRPATIENTPROBLEMS
FOR EACH ROW
BEGIN
IF (NVL(:old.ENCOUNTER_ID,0)<>0) AND (:new.STATUS=0) AND (:old.ENCOUNTER_ID<> :new.ENCOUNTER_ID) THEN
INSERT INTO EMRPATIENTPROBLEMSHISTORY
(PROBLEM_ID,ONSET_DATE,RESOLVED_DATE,PATIENT_ID,COMMENTS,PROBLEM_DESC,CREATOR,PROBLEM_CODE,PROBLEM_NAME,IS_CODED,
PRB_TYPE_ID,STATUS,ENCOUNTER_ID)
VALUES
(:old.PROBLEM_ID,:old.ONSET_DATE,:old.RESOLVED_DATE,:old.PATIENT_ID,:old.COMMENTS,:old.PROBLEM_DESC,:old.CREATOR,:old.PROBLEM_CODE,
:old.PROBLEM_NAME,:old.IS_CODED,:old.PRB_TYPE_ID,:old.STATUS,:old.ENCOUNTER_ID);
END IF;
END TR_Update_EMRPatientProblems;
/
its urgent

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-02 : 07:44:50

I guess PROBLEM_ID is primary key of table?

IF OBJECT_ID('TR_Update_EMRPatientProblems') IS NOT NULL
	DROP TRIGGER TR_Update_EMRPatientProblems
GO

CREATE TRIGGER	TR_Update_EMRPatientProblems
ON		EMRPATIENTPROBLEMS
AFTER UPDATE
AS

INSERT		EMRPATIENTPROBLEMSHISTORY 
		(
			PROBLEM_ID,
			ONSET_DATE,
			RESOLVED_DATE,
			PATIENT_ID,
			COMMENTS,
			PROBLEM_DESC,
			CREATOR,
			PROBLEM_CODE,
			PROBLEM_NAME,
			IS_CODED,
			PRB_TYPE_ID,
			STATUS,
			ENCOUNTER_ID
		)
SELECT		d.PROBLEM_ID,
		d.ONSET_DATE,
		d.RESOLVED_DATE,
		d.PATIENT_ID,
		d.COMMENTS,
		d.PROBLEM_DESC,
		d.CREATOR,
		d.PROBLEM_CODE,
		d.PROBLEM_NAME,
		d.IS_CODED,
		d.PRB_TYPE_ID,
		d.STATUS,
		d.ENCOUNTER_ID
FROM		inserted AS i
INNER JOIN	deleted AS d ON d.PROBLEM_ID = i.PROBLEM_ID
WHERE		d.ENCOUNTER_ID <>0
		AND i.STATUS = 0
		AND d.ENCOUNTER_ID <> i.ENCOUNTER_ID