Dumb trigger question

Table with last UpdatedBy Column and LastUpdatedOn Column.
Want to create Insert and Update Triggers that would automatically write
Logged in User Name and System Date Time to each field.
I Think User is SUSER_SNAME function and System date Is GETDATE() but can't
figure out how to write these two to row from within a trigger in table.
Any help greatly appreciated.
BobYou can use the inserted psuedo-table to identify the updated rows. One
method:
CREATE TRIGGER TR__Update_MyTable
ON MyTable FOR UPDATE
AS
UPDATE MyTable
SET UpdatedBy = SUSER_SNAME(),
LastUpdatedOn = GETDATE()
WHERE EXISTS
(
SELECT *
FROM inserted
WHERE inserted.PK = MyTable.PK
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>|||Use an INSTEAD OF trigger. The deleted pseudotable is a copy of any
existing rows that are about to be updated, the inserted pseudotable
contains the new values. The deleted pseudotable will be empty if the
operation was INSERT. The inserted pseudotable will be empty if the
operation was DELETE. Both pseudotables will have the same number of rows
if the operation was UPDATE. The reason you should use an INSTEAD OF
trigger is that it allows you to change values before it hits the database.
The only problem with them is that the cascade kludge won't coexist, which
in my opinion is a good thing.
In an INSTEAD OF UPDATE trigger, you simply issue an update statement:
UPDATE tableName
SET column1 = inserted.column1,
column2 = inserted.column2,
..,
UpdatedBy = SUSER_SNAME(),
LastUpdatedOn = GETDATE()
FROM inserted
WHERE primaryKeyColumn = inserted.primaryKeyColumn
The only time this is a problem is if you're using natural keys. If that's
the case, then you need to test for multiple rows
put code similar to this at the top of the trigger
--Don't put anything before this!!!
DECLARE @._ROWCOUNT INT SET @._ROWCOUNT = @.@.ROWCOUNT
IF @._ROWCOUNT = 0 RETURN
IF @._ROWCOUNT > 1 AND (UPDATE(primaryKeyColumn1) OR
UPDATE(primaryKeyColumn2))
BEGIN
ROLLBACK TRANSACTION
RAISERROR('ATTEMPT TO UPDATE Primary Key using set-based operation', 16, 0)
RETURN
END
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>|||Thank you both very much
Bob
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>

DTS: How to connect to hundreds of DBs dynamically

I need to release a few triggers and procedures to hundreds of databases
with the same structures.
I try the Dynamic Property in DTS, create an "Execution Task", add a
connection property with a source type as "Query", and the query is like
"select the database name from master..sysdatabases where name not in
('master','model','temp')".
Unfortunately, the package runs only on the first DB in the query.
How can I make it run on each of all databases?
Thank you.Create a connection to the server,
create an "Execute SQL Task" with your databases query in it -
in the "Output parameters" tab of the sql task use a rowset output
then loop through the values in an ActiveX script to perform your jobs.
http://www.sqldts.com/default.aspx?298
"Jluo" <jluost1@.yahoo.com> wrote in message
news:4224B876.5000304@.yahoo.com...
> I need to release a few triggers and procedures to hundreds of databases
> with the same structures.
> I try the Dynamic Property in DTS, create an "Execution Task", add a
> connection property with a source type as "Query", and the query is like
> "select the database name from master..sysdatabases where name not in
> ('master','model','temp')".
> Unfortunately, the package runs only on the first DB in the query.
> How can I make it run on each of all databases?
> Thank you.
>

DTS vs triggers

Hi,

I've got the following problem:

I'm using DTS to transfer fata from csv files into a table.
In the file there are often duplicated rows, therefore I made a
trigger
on the table.

The trigger works fine when i tried in query analyzer, but it not
launched
from the dts.

I've learned that i have to turn of the fast load option in the
properties of the transform data task object.
When I turn this option off, the task will fail with the following
message:

"The number of failing rows exceed the maximum specified.
Cursor operation conflict."

I wonder why this happens. There are no check constraints on the table
against duplication.

Thanks in advance for any helpBijesz wrote:
...
> "The number of failing rows exceed the maximum specified.
> Cursor operation conflict."
> I wonder why this happens. There are no check constraints on the table
> against duplication.

I may have an answer:

Let's assume you've saved your DTS job on the SQL Server, so that when
you are in Ent. Mgr. you can click on the DTS folder, click on the
'local packages' icon, and find your saved DTS job there.

Once there, you'll see the DTS job displayed as a set of 3 objects: 1)
the Excel icon for your source; 2) an arrow pointing to your MSSQL
destination, and; 3) your MSSQL destination.

If you right-click on the Excel or MSSQL objects and open the
Properties window, as I did, you'll be perplexed.

It finally occurred to me to right-click on that arrow and open-up yet
another Properties window there.

Once there, if you click on the 'Options' tab, you'll find a 'Max Error
Count' property. Nice place to hide it, eh?

hth,

victor dileo