Results 1 to 5 of 5

Thread: Trigger

  1. #1
    Join Date
    Jun 2007
    Posts
    25

    Unanswered: Trigger

    How do i write an convert an Oracle trigger to Microsoft sql server

    Oracle trigger:
    CREATE OR REPLACE TRIGGER zip_modify_oracle
    AFTER INSERT OR UPDATE
    ON Orders
    FOR EACH ROW
    BEGIN
    -- Find ZIP_CODE
    IF :new.ZIP_CODE is NULL then
    :new.ZIP_YN = 0;
    else
    :new.ZIP_YN = 1;
    END IF;
    END;

    Microsoft SQL sERVER:
    CREATE TRIGGER zip_modify_mirosoftsqlserver
    ON Orders
    FOR INSERT,UPDATE
    AS
    ???

    CAN ANYONE HELP?

  2. #2
    Join Date
    Jun 2007
    Posts
    25
    How close is this??

    CREATE TRIGGER zip_modify_mirosoftsqlserver
    ON Orders
    FOR INSERT,UPDATE
    AS
    IF UPDATE (ZIP_CODE) IS NULL
    BEGIN
    UPDATE ZIP_YN = 0
    else
    UPDATE ZIP_YN = 1
    END
    GO

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not anywhere close.
    That won't even compile. And you don't reference the virtual Inserted table either.

    Try this (substitute your actual primary key):
    Code:
    CREATE TRIGGER trOrders_UD
    ON Orders
    FOR INSERT,UPDATE
    AS
    BEGIN
    UPDATE	Orders
    SET	ZIP_YN = CASE WHEN ZIP_CODE IS NULL THEN 0 ELSE 1 END
    FROM	Orders
    	INNER JOIN Inserted on Orders.[PrimaryKey] = Inserted.[PrimaryKey]
    END
    GO
    Read the section on the virtual Inserted and Deleted tables in Books Online to understand how SQL Server processes triggers.
    Last edited by blindman; 07-03-07 at 00:41.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2007
    Posts
    25
    I realise my trigger was only running for a value which was null.Not what I required
    Last edited by superdrog; 07-03-07 at 02:51.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Superdrog - does your job interview test require you convert PL\SQL to T-SQL? The last question could conceivably have been for someone with only a bit of SQL experience - this is definately in a different ballpark.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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