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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Incoming parameter on Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fralo
Posting Yak Master

150 Posts

Posted - 09/25/2012 :  09:56:49  Show Profile  Reply with Quote
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 REPLICATION
AS
BEGIN
SET NOCOUNT ON;
END
GO

Thanks so much for your help.

Edited by - fralo on 09/25/2012 09:57:25

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  10:23:33  Show Profile  Reply with Quote
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.aspx

If 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.
Go to Top of Page

fralo
Posting Yak Master

150 Posts

Posted - 09/25/2012 :  10:43:33  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  11:04:27  Show Profile  Reply with Quote
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.aspx

Couple 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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/25/2012 :  11:15:18  Show Profile  Reply with Quote
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
Go to Top of Page

fralo
Posting Yak Master

150 Posts

Posted - 09/25/2012 :  11:20:15  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000