February 19, 2009 at 12:34 am
Hello there guys, I want to make an application on vb.net to monitor and show every new insert made on a specific table, but I am really not that literate either on vb.net or sql 2005, can anyone please help me a little bit with this project so I can get going....
I will need some kind of a Real Time Monitor for this table... Should it be like a live connection?? I dunno I am just guessing here... Would appreciatte some pointers....
Thanks
February 24, 2009 at 3:02 pm
Look up CREATE TRIGGER in Books Online (especially the examples at bottom of page). You can setup a trigger to perform some action when an insert is done on a table.
Another thing you can do is setup a sql server agent job to run a script or stored procedure that checks the number of rows in a table, and looks for changes. You would have to have another table that stores the current date/time, and the total number of rows, or maybe just the last ID or something like that.
February 25, 2009 at 2:20 am
February 25, 2009 at 7:21 am
I would definitely do this with a trigger on the table you want to monitor. You may not even need an application...just a report. If you want to be notified of changes you could do a data-driven subscription on the report.
If SQL 2008 is an option, check out the new change data capture features: http://msdn.microsoft.com/en-us/library/bb522489.aspx
February 25, 2009 at 11:22 am
The thing is that I am writing an application that whenever a record is inserted ona specific table I have to perform some other tasks, but I still dunno how to check when the record gets inserted...
February 25, 2009 at 11:28 am
jblanco (2/25/2009)
The thing is that I am writing an application that whenever a record is inserted ona specific table I have to perform some other tasks, but I still dunno how to check when the record gets inserted...
If they're database tasks, use a trigger.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2009 at 11:47 am
You could use an insert trigger to fire off a process that would notify your VB application that a row was added. The trigger will execute a SQL statement every time a row is inserted into the table you want to monitor. That action that is fired by the trigger could do a few things I can think of.
1. Insert a row into a table that your VB app monitors for changes...although if you do this, why not just have your VB app wake up periodically and check for new rows?
2. Call a web service that wakes your VB app up (or your VB app is the web service). This would probably need to be a CLR trigger and I think would achieve the best synchronicity between the two parts of your solution.
3. Place a message on a SQL Service Broker message queue that your VB app could pick up.
I'm sure others can think of other ways to implement this as well.
February 25, 2009 at 12:14 pm
Chris, I like your solution, I am using VS 2008 but I dont know how to implement those triggers, any help with the code?
February 25, 2009 at 12:45 pm
Couple steps back...
What kind of process do you need to run when a row is inserted?
What's going to be running those processes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2009 at 12:47 pm
To create a basic insert trigger you do something like this:
CREATE TRIGGER trig1
ON yourtable
AFTER INSERT
AS
INSERT INTO monitortable
SELECT somecolumns
FROM inserted
GO
Where "yourtable" is the table you are monitoring;
"monitortable" is the table you want to log the inserted rows to;
"somecolumns" are columns from "yourtable" you want to log;
and "inserted" is a special, virtual table name created by the trigger that gives you access to the row that was inserted.
BOL reference for triggers: http://msdn.microsoft.com/en-us/library/ms189799.aspx
BOL reference for CLR triggers: http://msdn.microsoft.com/en-us/library/938d9dz2(VS.80).aspx
BOL reference for Service Broker: http://msdn.microsoft.com/en-us/library/bb522893.aspx
February 25, 2009 at 12:50 pm
Excellent help but how do I do all that from the visual studio???
February 25, 2009 at 1:23 pm
Well, to create the triggers and/or set up service broker, you'd need to enlist a DBA's help. To build the CLR trigger, you would use Visual Studio to write the code and a DBA will need to get it installed on the SQL Server. To build a web-service, you would do that in a Visual Studio project.
I don't think I can go into all of the details to do all of that in a forum post but the links I sent you should get you started.
February 25, 2009 at 2:11 pm
ok, I created the trigger directly on te database table, how do I get the trigger response on my vb.net application???
This is the trigger I created...
CREATE TRIGGER [dbo].[trig1]
ON [dbo].[WAGERHEADER]
AFTER INSERT
AS
SELECT *
FROM inserted
February 25, 2009 at 3:14 pm
As I stated earlier, your trigger needs to do something that your VB program can read. If you said INSERT INTO sometable before where you have SELECT * from inserted in your trigger and you create "sometable", your VB program could watch that table for new rows and as I said, if you do that, why not just have your VB program scan the WAGERHEADER table periodically instead.
If you want to be more sophisticated and have the trigger push the data to your application, you could create a CLR trigger and call a web service with it.
Here's a link to an article all about triggers and how to create and deploy CLR triggers: http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005.all
February 25, 2009 at 3:29 pm
Before you continue, could you please answer Gails questions? You could be missing something very important because of tunnel vision.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply