Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 creating trigger in server-side code..
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLIsTheDevil
Posting Yak Master

USA
177 Posts

Posted - 10/01/2009 :  11:13:09  Show Profile  Reply with Quote
Hey all,

I have a question regarding triggers. When I create a trigger, it gets permenantly attached to the table in question, is that right? In other words, it's like creating a stored procedure, no different, correct?

Thank you.

SQLIsTheDevil
Posting Yak Master

USA
177 Posts

Posted - 10/01/2009 :  11:25:24  Show Profile  Reply with Quote
What I'm trying to do is insert a query into, say, Table A from server-side code and use a trigger to check if that row was actually inserted into Table A. If it wasn't, record the exact ad-hoc insert query used into, say, Table B for error-checking. Number one, don't know if this is possible. Two, it has to be for a specific insert query, not every single insert statement.

Thank you.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/01/2009 :  13:24:54  Show Profile  Reply with Quote
Man, this is just like some of the specs I get....

A trigger fires when a DML Actions occurs against a table

Now with that, what do you want to check



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 10/01/2009 :  13:34:17  Show Profile  Reply with Quote
quote:
Originally posted by SQLIsTheDevil

What I'm trying to do is insert a query into, say, Table A from server-side code and use a trigger to check if that row was actually inserted into Table A. If it wasn't, record the exact ad-hoc insert query used into, say, Table B for error-checking. Number one, don't know if this is possible. Two, it has to be for a specific insert query, not every single insert statement.

Thank you.



Why do you think the insert will fail in the first place?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/01/2009 :  13:39:25  Show Profile  Reply with Quote
Because it was an "ad-hoc" INSERT

You shouldn't allow Dynamic SQL



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

SQLIsTheDevil
Posting Yak Master

USA
177 Posts

Posted - 10/01/2009 :  14:19:37  Show Profile  Reply with Quote
Hey Brett,

What do you mean by shouldn't allow dynamic sql?

I may just use a stored procedure to execute the insert query and setup a trigger when the query is executed. Then, I can check the "inserted" table (i.e. select * from inserted...). Sounds a lot less complex than using ad-hoc queries, me thinks.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/01/2009 :  14:45:52  Show Profile  Reply with Quote
What does this mean then?

quote:

record the exact ad-hoc insert query used






Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.4 seconds. Powered By: Snitz Forums 2000