Post reply

Trigger

  • November 22, 2018 at 4:33 am

    #416512

    I have a 8 sql tables with 3 columns ( varchar,int, date time)

    I insert 1 under int column the other 2 columns are auto populated with text and date values.

    Each time I insert 1 into one of the tables the corresponding sql job is triggered off as this table has a trigger on it. Similarly I have 8 tables and 1 trigger respectively on those 8 tables which trigger 8 sql jobs.

    I want to have a single table which inserts 8 rows which in turn trigger of 8 individual sql jobs based on only 1 trigger on this table . Those 8 sql jobs should be triggered based on the text column values ( 8 rows inserted) at a time .How can I achieve this.

    I was thinking of having an intermediate table which upon inserting 1 record can auto insert 8 different records into the current table.

    Can i have some suggestions

    Thanks

  • November 22, 2018 at 4:45 am

    #2014329

    My suggestion is not to call jobs from triggers.  What happens if a job is already running when the trigger that calls it fires?  What if SQL Server Agent is not running, for any reason?

    If you insist on doing it like this, use eight IF...THEN blocks to call the appropriate job(s) depending on the inserted values in the text column.  Bear in mind that an INSERT statement can consist of any number of rows, so if each job needs to fire for each inserted row with a particular value then you'll need to code that in, probably with a cursor.  The more I think about this, the worse it sounds!  If you post table DDL, sample data, sample INSERT statements, trigger code and job code then we may be able to help you find a better solution.

    John

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply