Professional Web Applications Themes

CREATE TRIGGER - Microsoft SQL / MS SQL Server

Hi why do I get a syntax error near '[' for this trigger CREATE TRIGGER [t_fox_TIMERu] ON [dbo].[fox] FOR INSERT, UPDATE, DELETE AS DECLARE foxtimer datetime SET foxtimer = DATEADD ( hour , 5, (SELECT MAX(TIMER) FROM fox) ) INSERT INTO fox (TIMER, ADDRESS, EMPLOYEE, EMAIL, URL) VALUES (foxtimer, ' ', ' ', ' ', ' ') IF gv_fox.Displayrows[ =5] I created the global variable gv_fox in a DTS package...

  1. #1

    Default CREATE TRIGGER

    Hi

    why do I get a syntax error near '[' for this trigger

    CREATE TRIGGER [t_fox_TIMERu] ON [dbo].[fox]
    FOR INSERT, UPDATE, DELETE
    AS
    DECLARE foxtimer datetime
    SET foxtimer = DATEADD ( hour , 5, (SELECT MAX(TIMER)
    FROM fox) )
    INSERT INTO fox (TIMER, ADDRESS, EMPLOYEE, EMAIL, URL)
    VALUES (foxtimer, ' ', ' ', ' ', ' ')
    IF gv_fox.Displayrows[ =5]



    I created the global variable gv_fox in a DTS package
    Stephen Guest

  2. #2

    Default Re: CREATE TRIGGER

    As far as the syntax goes, use IF gv_fox.Displayrows = 5 instead of IF
    gv_fox.Displayrows[ =5]

    The '[' shown in the syntax detail information in SQL Server Books Online
    simply suggests it is an optional expression.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  3. #3

    Default Create Trigger


    Hello There,



    I am currently working on a Trigger that is visibly correct however i
    can not seem to understand why i get an error message

    when i try and save a record



    <Maximum stored procedure nesting level exceeded (limit 32).>



    This is the syntax for the Trigger



    create trigger ramBdaraApplicationProcess

    on ramApplicationProcess

    for insert,update as

    declare ramid varchar(15)

    select ramid = ram_id

    from inserted

    update ramApplicationProcess

    set date20 = date2

    where ram_id = ramid



    Any help/advice would be greatly appreciated.



    Cheers, Leo


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

  4. #4

    Default Re: Create Trigger

    The fact that you are using an UPDATE on the same table
    "ramApplicationProcess" is causing the trigger to fire again, since you have
    configured the trigger for INSERT and UPDATE. This kind of recursion is
    called "Direct Recursion". In order to prevent this problem (and effectively
    execute your trigger only once), you need to set the RECURSIVE_TRIGGERS
    database option to OFF. You need to use the "sp_dboption" command to achieve
    this. For example:

    EXEC sp_dboption 'yourDatabase', 'recursive triggers', 'OFF'

    Also note that you need to be a member of sysadmin or dbcreator or db_owner
    roles to run this command.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

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


    SriSamp Guest

Similar Threads

  1. XML.TRIGGER
    By Dan-C in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: February 28th, 03:55 PM
  2. Trigger
    By James in forum ASP Database
    Replies: 1
    Last Post: August 6th, 01:05 AM
  3. CREATE TRIGGER [testtrigger] ON *
    By Marc Fauser in forum Microsoft SQL / MS SQL Server
    Replies: 9
    Last Post: July 11th, 06:57 AM
  4. Trigger Help
    By Mike in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 9th, 07:28 PM
  5. Trigger key
    By Elisabeth Smith in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 2nd, 09:22 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