Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2014

    Unanswered: Dynamic changing datatbase in triggers of a table

    Preparing table with 3 columns
    tmp_table (viewname,column,condition,value)
    column 1 is viewname {format will be [database].dbo.[viewname]}
    column 2 is column
    column 3 is condition
    column 4 is value

    In triggers of the tmp_table want to put a check whether viewname exist or not.
    Now <datbase> is not known , and using below code with database as variable doesnt work in trigger
    PHP Code:
    select @db substring(viewname,1,charindex('.',viewname)-1from inserted
    @tmp_view substring(viewname ,charindex('.',viewname) +5,len(viewname)) from inserted
    if not exists (select 1 from @db.dbo.sysobjects where name = @tmp_view and type 'V')
    @errmesg "view doenst exist"
    raiserror 80000 @errmesg
       rollback transaction
    I tried below method as well which is using variable , In this also I can see that variable @lv_flag
    is not updated inside execute statement , It takes the last modified value outside execute.
    PHP Code:
    declare @db varchar(15)
    tmp_view varchar(25)
    lv_flag char(1)
    select @tmp_view  'test1'
    select @lv_flag 'F'
    execute("select " + @lv_flag " = 'K'  from abcd.dbo.sysobjects where name = '" +@tmp_view"' and type = 'V'")
    raiserror 80000 @lv_flag 

  2. #2
    Join Date
    Jan 2004
    Provided Answers: 4
    If you declare the variables in your first piece of code it looks like it should work in a trigger.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Dec 2013
    What Sybase db software are you using (ASE, SQLAnywhere, IQ, Advantage)?

    What version of said db software are you using?

  4. #4
    Join Date
    Dec 2013
    You've got an issue with what you're sending in the execute() call.

    I'd suggest you:

    1 - define a local variable (eg, @command varchar(2000))

    2 - build your SELECT statement in this variable (eg, select @command = 'select ...'

    3 - print @command (to see what you are actually submitting to execute())

    4 - then execute(@command)

    It shouldn't be too hard to see from the output of 'print @command' what your issue is.
    Last edited by markp64; 01-03-14 at 15:02. Reason: small edit

  5. #5
    Join Date
    Jan 2014


    Adaptive Server Enterprise/15.0.3

Tags for this Thread

Posting Permissions

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