Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Posts
    36

    Question Unanswered: Same Trigger, different table schema updates?

    Greetings, all! Can anyone assist with my current problem? Here's the deal:

    I have a web-based program that inserts data into a central table. What I want to do is on insert, take the row that is entered and push it to the "final destination" table. However, the final table could have any one of about 30 different schema names/owners! So basically:

    Data entered via web into dbo.DATA1
    Trigger on DATA1 pushes data into X.DATA1 if a certain column in the entered row equals the schema name for X. Or Y, or Z, etc.

    Not sure if I'm explaining this clearly or not. If so, can anyone help? I'm thinking what I need to do is create a trigger that uses a variable as the tableowner name, which it would get from a field in the inserted row of the first table. Does this sound right? Is there a better way?

    Thanks in advance!
    Dirk

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    You could extract the schema name from the inserted record, build a string with the insert instruction and then issue an EXECUTE(@<insert instruction>). Be warned that who or what ever issues the insert will have to wait till your trigger muddles through all the records. Don't forget to write your trigger to handle multipule rows with diffrent schema names. If you are looking at few or infrequent insertions this could work but I would watch it like a hawk.

    An option would be to write a stored procedure to handle all the inserts and run it every five min. or so. If the every x min. thing isn't good then add a trigger to schedule a job to run in the next 1 seconds. The job would then run your storeded procedure and your inserts into the x.DATA1 tables would be near real time. This allows you to decouple the insert into the dbo.DATA1 table and distibution of the inserted data. Your sp should continue to run as long as there are new records to process.

    Clear as MUD???

    Anyone else?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jun 2002
    Posts
    36
    Paul,

    Thanks again for the reply. Every X min is not really an option. This needs to be at or near real-time entry. There will probably be thousands of entries made in this table over the next few months, but likely at least 200-300 per day at times. So issuing an execute might not be the best route either.

    That leaves me with the stored procedure option. My main question is how do I assign a variable in any type of function (trigger, cursor, sp) to indicate a tableowner?

    There are 37 different tableowners for this application, but all reside within a single database (all have separate tables, however). So I need to somehow tell the process that when the "owner" field in the main table (or the inserted row) = "X", to update the final table(s) that are owned by "X" and so forth.

    That's where I'm stumped!
    Dirk

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    In T-SQL object references must be resolved when a trigger,procedure or function is saved. I wanted to say compiled but I think the code is tokenized and optimized. The exception to this is the EXECUTE statment, this is your "out" for dynamicaly changing object references. Even then the dynamic EXECUTE statment goes through the same process as a trigger, procedure or function.

    IMHO using an EXECUTE for doing a simple insert is okay. If you put this in a trigger you will probably be scanning the inserted temp table or extracting the data row at a time and calling the insert with the VALUES clause. Not to many chances for optimization.

    If you feel using EXECUTE is not an option you are looking at a big IF statment to work what table to insert into.

    If very near real time is an option I can outline how I setup a similar system to replicate data between multipul versions of SQL server, Ingress and Sybase.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Code:
    declare @inserted table (Owner varchar(10), Other int, stuff datetime)
    insert into @inserted values('ABC',1,getdate())
    insert into @inserted values('ABC',2,getdate())
    insert into @inserted values('ABC',3,getdate())
    insert into @inserted values('ABC',4,getdate())
    insert into @inserted values('ABC',5,getdate())
    
    insert into @inserted values('DEF',1,getdate())
    insert into @inserted values('DEF',2,getdate())
    insert into @inserted values('DEF',3,getdate())
    insert into @inserted values('DEF',4,getdate())
    insert into @inserted values('DEF',5,getdate())
    
    insert into @inserted values('GHI',1,getdate())
    insert into @inserted values('GHI',2,getdate())
    insert into @inserted values('GHI',3,getdate())
    insert into @inserted values('GHI',4,getdate())
    insert into @inserted values('GHI',5,getdate())
    
    print 'The following ''EXECUTE'' statments will be run inside your trigger.'
    print ''
    declare @owner varchar(10), @TSQL varchar(255)
    select @owner = min(owner) from @inserted
    while (@owner is not null) begin
      set @TSQL = 'insert into ' + @owner + '.DATA1 (ColA, ColB) select other, stuff from @inserted'
      raiserror('execute (''%s'')',0,1,@TSQL)
      select @owner = min(owner) from @inserted where owner > @owner
    end
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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