|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Newbie - help with trigger
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Newbie - help with trigger
|
|
| Tony Epton 2002-11-20, 9:23 am |
| I'm reading and re reading manuals but need some help to get going.
Say I have a table:
CostId, Id
CostDescription, text
DateTimeModified, date/time
UserId, text
I would like to write an update trigger to load current system date &
time in to the DateTimeModified and User Name in to UserId.
Could someone please get me started on how this would look ?
Would this result in recursive calls ?
How do I deal with situations where multiple records have been updated
but the trigger only fires once for the batch?
Many thanks in advance
Tony Epton
| |
| Vishal Parkar 2002-11-20, 9:23 am |
| 1)by default recursive trigger option is not enabled for
database you can check this by using.
sp_dboption '<db name>'
if its on you need to put it off.
2)Since SQL Server trigger fires at statement level you
may have to create a cursor in the trigger which joins
with temporary table INSERTED and then perform update
operation on table. I would suggest you to go for INSTEAD
OF TRIGGER.
--Vishal
>-----Original Message-----
>I'm reading and re reading manuals but need some help to
get going.
>
>Say I have a table:
>
>CostId, Id
>CostDescription, text
>DateTimeModified, date/time
>UserId, text
>
>I would like to write an update trigger to load current
system date &
>time in to the DateTimeModified and User Name in to
UserId.
>
>Could someone please get me started on how this would
look ?
>
>Would this result in recursive calls ?
>
>How do I deal with situations where multiple records have
been updated
>but the trigger only fires once for the batch?
>
>Many thanks in advance
>
>Tony Epton
>.
>
| |
| Tomislav Kralj 2002-11-20, 10:23 am |
|
<ace join_to ware@iinet.net.au (Tony Epton)> wrote in message
news:3ddb98c1.2229127@msnews.microsoft.com...
> I'm reading and re reading manuals but need some help to get going.
>
> Say I have a table:
>
> CostId, Id
> CostDescription, text
> DateTimeModified, date/time
> UserId, text
>
> I would like to write an update trigger to load current system date &
> time in to the DateTimeModified and User Name in to UserId.
UPDATE YourTable SET DateTimeModified = GETDATE(), UserID = suser_sid()
FROM YourTable y
INNER JOIN deleted d ON d.CostId = y.CostId
> Would this result in recursive calls ?
Yes, it would, but you can change this by setting nested triggers server
option to 0 (check RECURSIVE_TRIGGERS too, they should be set to OFF)
> How do I deal with situations where multiple records have been updated
> but the trigger only fires once for the batch?
You can use inserted/deleted virtual tables, they will be populated with
data either deleted or inserted. You can issue a query like
INSERT INTO TableA SELECT * FROM deleted for example. This would insert
all the records from deleted virtual table to TableA table.
Tomislav Kralj
tomislav.kralj1@zg.tel.hr
| |
| Tony Epton 2002-11-20, 7:23 pm |
| On Wed, 20 Nov 2002 16:22:22 +0100, "Tomislav Kralj"
<tomislav.kralj1@zg.tel.hr> wrote:
Many thanks
It all looks so clear and simple now that you have spelled it out for
me.
I am very impressed by the fact that you wrote the delete trigger for
me as well. - I guess it must be pretty obvious where I am heading
with this - a crude form of database synchronisation.
Many thanks
Tony
|
|
|
|
|