Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fralo
Posting Yak Master
161 Posts |
 Posted - 2012-09-25 : 09:56:49
|
| Hi,I'm coding my first trigger in sql server. I have a front-end application in which the user enters a value called PagerID. My trigger is supposed to check if that value exists within a table. If it does, row values must be updated. Otherwise, a new record is created.I'm not sure how to acquire the value that the user enters in the application. I've got I think the basic structure of the trigger down. How do I specify the incoming parameter?CREATE TRIGGER [dbo].[phpMyAdmin] ON [dbo].[ip_PagerProgramming] AFTER UPDATE NOT FOR REPLICATIONAS BEGIN SET NOCOUNT ON;ENDGOThanks so much for your help. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-25 : 10:23:33
|
| That does not sound like a situation where you would need/should use a trigger. Assuming that you are getting some data from the client application including a pager number, and assuming you are using that data to update a row in a table, couldn't you update or insert the a new row as part of updating the table?As to your question about how to acquire the value that the user enters in the application, you would need to write some code in the application to establish a connection with the database and send the information it needs to update. Assuming you are using SQL Server, this page has examples: http://msdn.microsoft.com/en-us/library/dw70f090.aspxIf you are not using SQL Server, you may get faster and better answers at another forum such as dbforums.com because this forum specializes in Microsoft SQL Server. |
 |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2012-09-25 : 10:43:33
|
| Thanks for your response. Your suggestion is without a doubt the recommended way under normal circumstances. The reason why we not doing it that way in this case is because the application involves code with which we aren't familiar. It would require a learning curve on our part to do it that way. Since we, in our department, are somewhat familiar with t-sql, we figured this would be the quickest route.So if you know how to reference this entered value from within the trigger, I would greatly appreciate it. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-25 : 11:04:27
|
You will have access to two virtual tables in the trigger. INSERTED and DELETED. INSERTED contains the new values, DELETED contains the original values. There is some documentation and examples here: http://msdn.microsoft.com/en-us/library/ms191300.aspxCouple of other things to keep in mind:1. Impact of recursive triggers and nested triggers. Since you will be updating the same table that the update statement updated, if recursive triggers is enabled that can pose a problem. However, by default it is turned off - nonetheless you might want to check that:SELECT is_recursive_triggers_on FROM sys.databases WHERE NAME = 'yourdbname' 2. Whether there are any other triggers on the table. If there are, the order in which the triggers will fire is not guaranteed. So you may need to consider their peaceful coexistence. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-09-25 : 11:15:18
|
quote:
Originally posted by sunitabeck
<snip>2. Whether there are any other triggers on the table. If there are, the order in which the triggers will fire is not guaranteed. So you may need to consider their peaceful coexistence.
Kinda sorta: http://msdn.microsoft.com/en-us/library/aa260304(v=sql.80).aspx |
|
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2012-09-25 : 11:20:15
|
| No, I won't be updating the same table. Sorry if I meant to suggest otherwise. I will be updating a table on another server actually, so I had to create a linked server. |
|
|
|
|
|
|
|
|
Incoming parameter on Trigger