Execute task based on INSERT - but without triggers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Execute task based on INSERT - but without...

Execute task based on INSERT - but without triggers

Rate Topic

Display Mode

Topic Options

Author

Message

Badesvin

Posted Sunday, February 03, 2013 4:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 08, 2013 2:45 PM
Points: 2, Visits: 2

Let me try with first defining what I want to achieve, as I'm very new to SQL I don't know what tools/methods to use.

When an insert happens in a specific table that has a specific value (eg. "Info") run a script (iim thinking sqlclr?) that takes information from that insert with it (computer name, user SID in my case) and go to that computer name to get volatile information stored in the registry on that computer name.
Once that info is retrieved, insert it back into a table (different from the one that triggered the script to begin with) but link it with the ID field from the insert that triggered.

I have tried with a trigger but if that fails my insert never happens. IMO trying to understand if DDL/DDM and sqlclr could help me here?

Post #1415008

GilaMonster

Posted Sunday, February 03, 2013 7:40 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 38,095, Visits: 30,390

That's a trigger, well the insert into another table part is.

For an insert to read from the client machine's registry, you will need to embed that functionality into the client application, as at the point that the insert happens in SQL, there's no call back to the client.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1415017

Badesvin

Posted Sunday, February 03, 2013 10:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 08, 2013 2:45 PM
Points: 2, Visits: 2

If possible i would like to avoid triggers, as if something happens to my trigger (fails for whatever reason) the original INSERT i triggered on wont happen. Thats why i thought of sqlclr - if i understand that correctly i could write sqlclr code that would make that call to the remote computer, gather the data from the registry and insert it back into a table or am i missing something there?

Post #1415027

GilaMonster

Posted Sunday, February 03, 2013 10:18 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 38,095, Visits: 30,390

Missing, no, seriously, majorly overcomplicating, yes. For what you describe you'll need unsafe CLR, elevated permissions on the client computer (so that SQL can talk to the client machine), lots of error handing, complex transactional handling and you'd still need a trigger to call it.

Personally I'd just have the client app responsible for both inserts if registry data from the client is necessary

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1415028

« Prev Topic | Next Topic »

Permissions