batching an update, use TRANSACTION?

I have a storedproc that does several UPDATEs to a table, some of which fire
and some of which don't based on various IFs through the proc. There's a
maximum f three, it's not THAT complex.
However there is a trigger on the table that records all UPDATEs into a
separate auditing log. I would like there to be only one trigger fire
regardless if one, two or three of the UPDATEs were called.
Does TRANSACTION do this? I do _not_ want to turn off the triggers in the
proc.
MauryIf I understand you correctly, TRANSACTION will not do it. Can you put in
some logic in your trigger to check whether a previous UPDATE has already
fired the trigger?
Linchi
"Maury Markowitz" wrote:
> I have a storedproc that does several UPDATEs to a table, some of which fire
> and some of which don't based on various IFs through the proc. There's a
> maximum f three, it's not THAT complex.
> However there is a trigger on the table that records all UPDATEs into a
> separate auditing log. I would like there to be only one trigger fire
> regardless if one, two or three of the UPDATEs were called.
> Does TRANSACTION do this? I do _not_ want to turn off the triggers in the
> proc.
> Maury|||"Linchi Shea" wrote:
> If I understand you correctly, TRANSACTION will not do it. Can you put in
> some logic in your trigger to check whether a previous UPDATE has already
> fired the trigger?
I guess, but only with peril.
Maury|||"Linchi Shea" wrote:
> If I understand you correctly, TRANSACTION will not do it. Can you put in
> some logic in your trigger to check whether a previous UPDATE has already
> fired the trigger?
Can I perhaps wrap the individual fields of the update in some sort of
conditional, and thereby combine them into one larger statement? Everything
is already loaded into local vars.
Maury

Batch file to fire Scheduled Job

Hi there,
I am trying to write a batch file that can run a scheduled Job that is
already written in SQL server. I know how to run a package using DTSRun
command from a dos prompt, but, is there any command to fire a scheduled
job. or run a stored procedure.
Thanks in advance,
Sree
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!you can use sp_start_job along with OSQL commandline utility
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"sk" <someone@.hotmail.com> wrote in message
news:%23BpimNekEHA.3536@.TK2MSFTNGP12.phx.gbl...
>
> Hi there,
> I am trying to write a batch file that can run a scheduled Job that is
> already written in SQL server. I know how to run a package using DTSRun
> command from a dos prompt, but, is there any command to fire a scheduled
> job. or run a stored procedure.
> Thanks in advance,
> Sree
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Use osql utility.
Sample syntax:
osql -S MYSERVER -E -d msdb -Q "sp_start_job @.job_name
= 'DB Backup Job for DB Maintenance Plan ''TEST'''"
>--Original Message--
>
>Hi there,
>I am trying to write a batch file that can run a
scheduled Job that is
>already written in SQL server. I know how to run a
package using DTSRun
>command from a dos prompt, but, is there any command to
fire a scheduled
>job. or run a stored procedure.
>Thanks in advance,
>Sree
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

Batch file to fire Scheduled Job

Hi there,
I am trying to write a batch file that can run a scheduled Job that is
already written in SQL server. I know how to run a package using DTSRun
command from a dos prompt, but, is there any command to fire a scheduled
job. or run a stored procedure.
Thanks in advance,
Sree
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
you can use sp_start_job along with OSQL commandline utility
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"sk" <someone@.hotmail.com> wrote in message
news:%23BpimNekEHA.3536@.TK2MSFTNGP12.phx.gbl...
>
> Hi there,
> I am trying to write a batch file that can run a scheduled Job that is
> already written in SQL server. I know how to run a package using DTSRun
> command from a dos prompt, but, is there any command to fire a scheduled
> job. or run a stored procedure.
> Thanks in advance,
> Sree
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Basic Trigger Firing Question

If I insert 20 records into a table which has an INSERT trigger on it, does the trigger fire once for each record, or once at the end of all the inserts?

I seem to be having a problem where the trigger is firing only for some of the inserted records but not others, seemingly randomly (although I know there must be some logical explanation for how it's choosing which record to fire under).

Thanks =) MattHere's how I ended up getting multirow inserts to work properly using an INSTEAD OF trigger rather than a AFTER trigger.

-- INSTEAD OF Trigger That Fires Single-Row AFTER Triggers for a Table with IDENTITY

CREATE TRIGGER trg_T1IOIS ON T1 INSTEAD OF INSERT
AS

SELECT IDENTITY(int, 1, 1) AS key_col, data_col
INTO #t1
FROM inserted

DECLARE @.key AS int
SELECT @.key = MIN(key_col) FROM #t1

WHILE @.key IS NOT NULL
BEGIN

INSERT INTO T1
SELECT data_col FROM #t1 WHERE key_col = @.key

SELECT @.key = MIN(key_col)
FROM #t1
WHERE key_col > @.key

END
GO|||Each Trigger written should be able to deal with either a Single Insert/Delete/update or Bulk Insert/Delete/update

I tend to use the following Format :-

BEGIN
IF @.@.RowCount =1
BEGIN
/*DO SINGLE ROW OPERATION*/
END
ELSE
BEGIN
/*DO MULTI ROW OPERATION*/
END
END

Generally I alter the TSQL to cope with both eventualities
and I believe it is good practice to apply this to ALL Triggers unless of course there are special cicumstances.

Hope this Helps

GW