|
Hi all,
Can anyone tell me if database triggers are possible with msde 2000? I want to have a trigger in my vb.net(2003) application whenever someone inserts a row in a particular table i get a trigger. Working example would be more than welcome or direct me to some articles.
thanx in advance
Qaiser
|
|
|
|
|
This is a quote from MSDN[^] which I found using this Google search: msde 2000 trigger .
When to Choose MSDE<br />
<br />
MSDE is an attractive option for upgrading existing Jet applications to improve scalability or for creating new desktop and shared database solutions that can be easily migrated to full SQL Server, as the solutions must need to scale.<br />
<br />
SQL Server compatibility<br />
<br />
MSDE data engines are fully compatible with SQL Server. Thus, tables, stored procedures, triggers, and any other database objects created in MSDE will operate without modification in a SQL Server database. In contrast many Jet objects, such as queries, cannot be migrated to SQL Server without modification. If you are considering migrating to SQL Server in the future, or it is likely that your database application will exceed the limitations of Jet, you should choose MSDE. Doing so will provide you with the simplest path to upsizing the database to SQL Server without changing a single line of code.
...Steve
1. quod erat demonstrandum
2. "Give a man a fish and you've fed him for a day. Teach him how to fish and you've fed him for life." I read that somewhere once
(Translation: I'll show you the way, but not write the code for you.)
|
|
|
|
|
Hi,
Creating a trigger using the Server Explorer window.
Write the following code in the trigger text editor.
CREATE TRIGGER SampleTrigger
ON dbo.Fresh
INSTEAD OF INSERT
AS
DECLARE @id varchar (10)
SELECT @id=id FROM Inserted
IF (NOT EXISTS (SELECT Fresh.id FROM Fresh WHERE Fresh.id=@id))
INSERT INTO Fresh
SELECT id,sname FROM inserted
ELSE
PRINT 'can not add to the database'
A trigger is a procedure that SQL server invokes automatically when certain changes applied to the data. We can think of triggers as VB>Net event handlers for onUpdate,onInsert, and onDelete events.
More:Refer this title: "Database programming using vb.net and sql server 2000"
http://www.vkinfotek.com
Regards
Bhar
|
|
|
|
|
Hi Krisky,
Thanx for your reply but i already know this bit creating a trigger in database. Thing i want to know is how to get this event in you vb.net application? Say i created a trigger in a table called sales and when ever someone insert a row in it i get an event in my application. I know this makes database slow, it's not a recommended way etc etc but every thing is on a local system, there are few applications running on a system and eachone updates/insert records in the database, so i want in my application whenever some other application enters data in a specific table in database(i know other ways of interprocess communication in .net but those other apps are third party on which i have no control) i get an event in my vb.net/c# application.
Qaiser.
|
|
|
|
|
You won't get an event fired in your VB.NET app. It just does not work that way for the reasons you have already stated plus more - including the fact that SQL Server and your app are in different processes.
What you could do is run a CMD, BAT or EXE from your trigger code which itself calls your app to do whatever, but even that gets tricky and prone to performance issues.
You could just monitor the SQL transaction log (no forget that - you'll probably need 3rd party software to do it or else delve very deep into SQL Server innards).
How about writing a row to a separate table whenever your trigger fires? Your app can then monitor that table for new entries every 30 seconds or so and act accordingly.
I think there are some very basic examples of how to do soem of these things in the SQL Books Online - installed when you install SQL Server client and/or dev tools on your machine.
...Steve
1. quod erat demonstrandum
2. "Give a man a fish and you've fed him for a day. Teach him how to fish and you've fed him for life." I read that somewhere once
(Translation: I'll show you the way, but not write the code for you.)
|
|
|
|
|
Steve,
Thanx for your reply, your suggestion is good but it is not applicable in my situation, why? because i need the trigger/event in my app as soon as database entry is entered so can't wait for 30 seconds etc.
Anyway thanx for you guys to help.
Qaiser.
|
|
|
|
|
What's so important about trapping the event during the trigger execution?
...Steve
1. quod erat demonstrandum
2. "Give a man a fish and you've fed him for a day. Teach him how to fish and you've fed him for life." I read that somewhere once
(Translation: I'll show you the way, but not write the code for you.)
|
|
|
|
|
Steve,
Thanx for your reply, your suggestion is good but it is not applicable in my situation, why? because i need the trigger/event in my app as soon as database entry is entered so can't wait for 30 seconds etc.
Anyway thanx for you guys to help.
Qaiser.
|
|
|
|
|
You're thinking about SQL Server Notification Services[^].
But I don't know if it'll work with the Express or MSDE editions of SQL Server.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Yes Dave it is msde, i know it is possible with new .net 2.0 but i m developing in .net 1.1(vs.net 2003)
Qaiser
|
|
|
|
|
You mis-understood.
Triggers do not call your VB.NET program. Trigers are fired internal to the database when certain events happen, in the database. For instance, if you setup a Trigger to run when a record is added to a table, the Trigger can call a stored procedure you write in the database, not your VB.NET code.
Now, in SQL Server 2005 (any edition), stored procedures can now be written in any managed language, but they are still stored procedures stored and called inside the database.
Notification Server is something that runs on top of SQL Server that provides the funtionality to send notifications of database changes to external applications. This has nothing to do with Triggers! A Notification is completely independant of a Trigger.
How this works is your application code creates an object that contacts the Notification Server and subscribes to certain notification events that you define for your database. When those events occur, the object you created will fire off events you can handle in your VB.NET code.
You can find out more about how this works, complete with examples(!), by reading through the documentation on Notification Server. All you have to do is follow the link in my previous post.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hi dave,
By events i did meant same thing which you just mentioned, i know it is going to be some object attached or register with the sql server to get trigger(or whatever you call). The problem is that how to do this-haven't found any working example code, which am looking for...
Qaiser
|
|
|
|
|
You mean the samples and links found under these[^] links...
I would highly suggest reading through the Getting Started section before you move on the Development section or even double-click Visual Studio.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Many thanx dave,
your reply helps solve my problem.
Qaiser.
|
|
|
|
|
You can use the SQL intrinsic stored procedure xp_cmdshell in a trigger to call an external app. That app could then notify your app that a change has occured.
Or you could write an app that monitors the windows event log and put xp_logevent call in your trigger to send an alert to the event log.
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
Thanx Duncan,
I red on a few places to use xp_cmdshell but it don't know how to do it, haven't found any sample code and also i think it can only be done with c++(non .net languages). Can you please refer me to somewhere where i can find some sort of an example
Qaiser
|
|
|
|
|
I have developed a Windows Service, which send daily SMS using Clickatell SMS Gateway and getting values from remote MS SQL server but the problem is service works fine for some time like 10 days, some times 5 days, some times just for 2 days but the service is not stable, it don’t work for a long time, I am in a deep trouble kindly help me as soon as possible, I'll be very thankful to you all, especially a person who will help me.
|
|
|
|
|
Hisham Javed,
Check to see if you are using the right kind of timer for the job..
or you could post the code so we can see what your service is doing.
progload
|
|
|
|
|
Well, thanks for response. I am sending you the only source code, Please check this code and suggest me a better approch if there is any. i have sent you an email with source zip file code
Thank you very much for taking interest in my problem.
-- modified at 17:56 Friday 10th March, 2006
|
|
|
|
|
Maybe you have a memory leak chewing up resources - or possibly un unhandled exception?
Have you inspected the event logs for any clues?
...Steve
1. quod erat demonstrandum
2. "Give a man a fish and you've fed him for a day. Teach him how to fish and you've fed him for life." I read that somewhere once
(Translation: I'll show you the way, but not write the code for you.)
|
|
|
|
|
Hello!
My aim is to use one of DLL file what resides under the application's startup path. However the declaration below is looking for the dll file under the system directory of windows. If you have any idea, how to declare a dll file with a speific path, please let me know. Application.StartupPath cannot be used with a constant variable.
Thank You!
Public Declare Function kWabOpen Lib "wab.dll" (ByRef kw As tWab) As Boolean
|
|
|
|
|
B Journey,
The default path your program look to find a dll
is normaly, in this order:
If it is registered... right to location where it is registered.
Otherwise:
1.Directory containing the .exe file
2.Current directory
3.Windows system directory (often but not always \Windows\System)
4.Windows directory (not necessarily \Windows)
5.Path environment variable
Do you have it registered? if you do, unregister it and put it in the folder with your applications .exe file.
progload
|
|
|
|
|
Thanks Progload!
Sorry I didn't have time to return you according wab related project which we suppose to be working on it. My father passed away and I couldn't find time to work on it. Finally I found kwab.dll file which is free to use it even commercially.
I successfully fixed the errors in the program because of version differencies. Now it is working! However, it is looking kwab.dll file under windows/system32 directory. I put it under executable file's directory but it didn't work out.
I didn't register the kwab.dll that what is confusing me...
Thanks!
|
|
|
|
|
Interesting...
I downloaded the kwab1.2.zip from the authors site..
In the note.txt file it states:
"The dll must reside
- in the same folder of the executables that use it
or
- in the system32 directory in the windows folder (C:\WINNT or C:\WINDOWS)"
Is how you declared it correct? I noticed in your post you put wab.dll not kWab.dll
"Public Declare Function kWabOpen Lib "wab.dll" (ByRef kw As tWab) As Boolean"
Shouldn't it be declared:
Public Declare Function kWabOpen Lib "kWab.dll" (ByRef kw As tWab) As Boolean
progload
|
|
|
|
|
Thanks progload!
I have solved the problem it was my mistake. It was out of my attention. The dll file wasn't under the bin directory. I moved the file under the bin directory and now it is working!
Thank You!
|
|
|
|