Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    9

    Unanswered: Help Required in Giving sql server Trigger into Oracle

    Hi,
    Please help me in giving out the same in to Oracle.

    Code:
    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version

    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2010
    Posts
    9
    Yeah mine is a Oracle 10g and am not much familar using Oracle .Just please help me out in giving that into oracle.
    some how tried like this.But i dont know in using with deleted

    CREATE OR REPLACE TRIGGER TR_DELETE_EMR ON EMRTransactions
    FOR EACH ROW
    BEGIN
    DELETE FROM EMRCOTrans e JOIN DELETED d
    ON TO_CHAR(e.EMR_TRANSACTION_ID )= TO_CHAR(d.EMR_TRANSACTION_ID)
    WHERE d.ROLE_NAME IN ('CONSULTANT');
    END;
    /
    Last edited by rajasekhar857; 03-02-10 at 11:40. Reason: changes

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I don't know what you have (Tables or data).
    I don't understand what you want/expect/desire as results.
    I refuse to waste my time guessing at possible solutions.
    Therefore no assistance is possible from me.
    Perhaps others can do better.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Why are you casting d.EMR_TRANSACTION_ID as varchar(20) and e.EMR_TRANSACTION_ID as varchar(50)? Smells like a data truncation issue to me.

  6. #6
    Join Date
    Mar 2010
    Posts
    9
    ok can ypou please help me for oracle?

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by rajasekhar857 View Post
    ok can ypou please help me for oracle?
    You are aware that this is all documented in the manual?

    Here
    http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2235611

    and here
    http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#ADFNS012


    If you read the above documents, is very easy to come up with the following solution:

    Code:
    CREATE OR REPLACE TRIGGER TR_DELETE_EMR 
    BEFORE DELETE ON EMRTransactions 
    FOR EACH ROW
    WHEN (ROLE_NAME = 'CONSULTANT')
    BEGIN
      DELETE FROM EMRCOTrans e
       WHERE e.EMR_TRANSACTION_ID = :old.EMR_TRANSACTION_ID
    END;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •