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 |
|
whiteknight
Starting Member
3 Posts |
 Posted - 2009-04-30 : 11:27:58
|
| Hi I am trying to:Setup a Trigger on Table1 which, when record Insert is done, will call a Stored Procedure (this will write some details to Table2).I need to know:1. The Stored Procedure - How to get the Primary Key (from Table1) and write this to Table2. Not sure about @@IDENTITY and SCOPE_IDENTITY().2. The Storedprocedure - How to get the current date to write to Table2. I think I can use the date on the server for this, but don't know how to get it.3. The Trigger - How to call the Storedprocedure from the Trigger.Can anyone please help with all, or some of these? |
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-30 : 11:33:55
|
| Regarding #2, use the GETDATE() function |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-04-30 : 11:52:02
|
| From BOL:DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. So you can use those teo tables to get the data that was inserted(inserted or updated) including the PK.I've never called a stored procedure from a trigger, but I think you just call it like you would calling a stored proc from anotehr stored proc. |
|
|
|
whiteknight
Starting Member
3 Posts |
Posted - 2009-04-30 : 12:38:58
|
quote:
Originally posted by Lamprey
From BOL:DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. So you can use those teo tables to get the data that was inserted(inserted or updated) including the PK.I've never called a stored procedure from a trigger, but I think you just call it like you would calling a stored proc from anotehr stored proc.
Thanks for your reply!I am a newbie - Don't know about these 'special tables' - How do I access them and will this affect if other code is also accessing them? |
|
|
|
whiteknight
Starting Member
3 Posts |
Posted - 2009-04-30 : 12:41:56
|
quote:
Originally posted by whitefang
Regarding #2, use the GETDATE() function
Am Newbie - not sure how this GETDATE() function works. Could you please tell me how I use this to get the date into a field, so can write to Table2? |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-04-30 : 12:46:23
|
| You may benefit by the links following the first one in my signature.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQL or How to sell Used CarsFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-04-30 : 12:56:47
|
| Also, plenty of discussion and examples in topics returned from forum search on: audit triggerBe One with the OptimizerTG |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-04-30 : 12:58:41
|
quote:
Originally posted by whiteknight
quote:
Originally posted by Lamprey
From BOL:DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. So you can use those teo tables to get the data that was inserted(inserted or updated) including the PK.I've never called a stored procedure from a trigger, but I think you just call it like you would calling a stored proc from anotehr stored proc.
Thanks for your reply!I am a newbie - Don't know about these 'special tables' - How do I access them and will this affect if other code is also accessing them?
In order to access them you just use them like normal tables. I'd suggest that you look at "Implementing DML Triggers" in Books Online.There shouldn't be any issue with contention as they only exist with a specific scope. |
|
|
|
|
|
|
|
|
Stored Procedure and Trigger