Professional Web Applications Themes

Help with SQLServer trigger - Microsoft SQL / MS SQL Server

I'm having trouble compiling this trigger. Can anyone help me? CREATE TRIGGER calculate_sus ON [dbo].[JOB_TEJAS] AFTER INSERT AS BEGIN DECLARE sus INT; DECLARE unix_start_date INT; DECLARE unix_end_date INT; DECLARE nodes_reserved INT; DECLARE status VARCHAR; DECLARE id INT; SELECT unix_start_date = (SELECT unix_start_date FROM Inserted); SELECT unix_end_date = (SELECT unix_end_date FROM Inserted); SELECT nodes_reserved = (SELECT (nodes_reserved * 2) FROM Inserted); SELECT status = (SELECT status FROM Inserted); SELECT id = (SELECT id FROM Inserted); IF ((unix_start_date <> 0) AND (unix_end_date <> 0) AND (status = 'E')) BEGIN sus = ((unix_end_date - unix_start_date) * (nodes_reserved * 2)); END UPDATE job_tejas SET ...

  1. #1

    Default Help with SQLServer trigger


    I'm having trouble compiling this trigger. Can anyone help me?



    CREATE TRIGGER calculate_sus ON [dbo].[JOB_TEJAS]

    AFTER INSERT

    AS

    BEGIN



    DECLARE sus INT;

    DECLARE unix_start_date INT;

    DECLARE unix_end_date INT;

    DECLARE nodes_reserved INT;

    DECLARE status VARCHAR;

    DECLARE id INT;



    SELECT unix_start_date = (SELECT unix_start_date FROM Inserted);

    SELECT unix_end_date = (SELECT unix_end_date FROM Inserted);

    SELECT nodes_reserved = (SELECT (nodes_reserved * 2) FROM Inserted);

    SELECT status = (SELECT status FROM Inserted);

    SELECT id = (SELECT id FROM Inserted);



    IF ((unix_start_date <> 0) AND (unix_end_date <> 0) AND
    (status = 'E'))

    BEGIN

    sus = ((unix_end_date - unix_start_date) * (nodes_reserved * 2));

    END



    UPDATE job_tejas

    SET sus_charge = sus

    WHERE id =id;

    END



    END

    GO


    --
    Posted via http://dbforums.com
    scohen Guest

  2. #2

    Default Re: Help with SQLServer trigger

    Please always tell us what version you are using. Also, if something is 'not
    compiling', let us know what the symptoms are. Are you getting an error?
    What does it say?

    Without more info, I can see one big problem and several smaller ones.

    ALL variable assignments that are not a simple single value assignment must
    use SELECT

    so you need to change your assignment to sus to be:

    SELECT sus = .......


    Also, if you use an assignment based on a subquery, you must be sure that
    the subquery will return only one row.

    SELECT unix_start_date = (SELECT unix_start_date from Inserted)

    If there is more than one row in Inserted, the above will give you an error
    upon execution.

    Good Luck

    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    www.SolidQualityLearning.com


    "scohen" <com> wrote in message
    news:com... 


    Kalen Guest

  3. #3

    Default Re: Help with SQLServer trigger

    Scohen,

    If you can ever insert more than one row at a time to JOB_TEJAS,
    then only one will be processed by variations on your current design.

    When code or decisions are complex then you can use a cursor, in
    which case using internal variables ... makes sense. You can (and should)
    also select multiple columns into variables using commas to seperate, rather
    than have individual selects.

    For simpler code you don't need this:

    CREATE TRIGGER calculate_sus ON [dbo].[JOB_TEJAS]
    AFTER INSERT
    AS

    UPDATE j
    SET j.sus_charge = (i.unix_end_date - i.unix_start_date) * i.nodes_reserved * 2
    FROM job_tejas j
    INNER JOIN inserted i
    on i.id = j.id
    where i.unix_start_date <> 0
    AND i.unix_end_date <> 0
    AND i.status = 'E'

    Regards
    AJ

    "scohen" <com> wrote in message news:com... 


    Andrew Guest

  4. #4

    Default Re: Help with SQLServer trigger

    Not tested but something like this might do what you are wanting to do:

    CREATE TRIGGER calculate_sus ON [dbo].[JOB_TEJAS]
    AFTER INSERT
    AS

    UPDATE job_tejas
    SET sus_charge = (select ((unix_end_date - unix_start_date) *
    (nodes_reserved * 2)) as sus_charge
    from inserted
    WHERE id in (select id from inserted where unix_start_date <> 0 AND
    unix_end_date <> 0 AND
    status = 'E')

    HTH

    --
    Ray Higdon MCSE, MCDBA, CCNA
    ---
    "scohen" <com> wrote in message
    news:com... 


    Ray Guest

Similar Threads

  1. [Macromedia][SQLServer JDBC Driver][SQLServer]Internal
    By gsmadman in forum Coldfusion Database Access
    Replies: 1
    Last Post: February 9th, 04:53 AM
  2. ASP SQLSERVER PROB:
    By Anthony in forum ASP Database
    Replies: 4
    Last Post: October 1st, 11:02 PM
  3. Connecting ASP.NET to SQLServer,Somebody,Please!!
    By Mohammad-Reza in forum ASP Database
    Replies: 2
    Last Post: April 20th, 01:28 PM
  4. Insert trigger sqlserver 2000 and Visma Salesoffice
    By Kathinka in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 10:58 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139