Insert trigger changing record

Hello all!
I want to create an insert trigger to change some fields of the inserted
record. I want to put in two fields the system date and system time.
When I try to update Inserte table I get an error telling me I cannot
update inserted tables.
Can anyone give me a hand on this?
CREATE TRIGGER [Transactions_Insert] ON [dbo].[Transactions]
FOR INSERT
AS
update Inserted
set CreationDate = dbo.idlog_date(current_timestamp)
--
Function idlog_date returns the date in my format.
Thanks in advance,
Hugo MadureiraHugo Madureira wrote:
> Hello all!
> I want to create an insert trigger to change some fields of the inserted
> record. I want to put in two fields the system date and system time.
> When I try to update Inserte table I get an error telling me I cannot
> update inserted tables.
> Can anyone give me a hand on this?
>
CREATE TRIGGER [Transactions_Insert] ON [dbo].[Transactions]
FOR INSERT
AS
UPDATE dbo.transactions
SET CreationDate = dbo.idlog_date(current_timestamp)
GO
It seems like overkill to use a trigger for this. Have you considered
declaring a DEFAULT value instead: DEFAULT CURRENT_TIMESTAMP.

> Function idlog_date returns the date in my format.
A DATETIME column doesn't have a "format". Why store the date as
anything other than DATETIME or SMALLDATETIME?
David Portas
SQL Server MVP
--|||Yes, thats right. You have to update the original data which is already
store in there.
UPDATE Transactions
SET CreationDate = dbo.idlog_date(current_timestamp)
FROM Transactions T
INNER JOIN INSERTED I
ON T.<YourprimaryKey> = I.<YourprimaryKey>
HTH, Jens Suessmeyer

Insert to a table

Can I do an insert from table A to table B only columns that are in A but not
in B and changing them before insert? I want the primary key of rows in A
that don't exist in B to be inserted in B, but I want to flag them in a
couple of other columns. I see that I can either use an Insert query with
select statement where all the columns are inserted as is or Insert...Values
where all are manual values. Is there a statement that can be a combination
of these two?
Thanks
You should be able to formulate an INSERT SELECT statement to do what you
want:
INSERT INTO TableB (col1, col2, col3, col4, ...)
SELECT colA, 'some value', 1234, colB, ...
FROM TableA
WHERE ...
David Portas
SQL Server MVP
|||On Mon, 13 Dec 2004 15:01:01 -0800, Niles wrote:

>Can I do an insert from table A to table B only columns that are in A but not
>in B and changing them before insert? I want the primary key of rows in A
>that don't exist in B to be inserted in B, but I want to flag them in a
>couple of other columns. I see that I can either use an Insert query with
>select statement where all the columns are inserted as is or Insert...Values
>where all are manual values. Is there a statement that can be a combination
>of these two?
>Thanks
Hi Niles,
You mean something like this?
INSERT INTO TableB (KeyColumn, OtherColumn, ThirdColumn)
SELECT KeyColumn, OtherColumn, 'Literal value'
FROM TableA AS a
WHERE NOT EXISTS
(SELECT *
FROM TableB AS b
WHERE b.KeyColumn = a.KeyColumn)
or alternatively:
INSERT INTO TableB (KeyColumn, OtherColumn, ThirdColumn)
SELECT a.KeyColumn, a.OtherColumn, 'Literal value'
FROM TableA AS a
LEFT OUTER JOIN TableB AS b
ON b.KeyColumn = a.KeyColumn
WHERE b.KeyColumn IS NULL
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Insert to a table

Can I do an insert from table A to table B only columns that are in A but not
in B and changing them before insert? I want the primary key of rows in A
that don't exist in B to be inserted in B, but I want to flag them in a
couple of other columns. I see that I can either use an Insert query with
select statement where all the columns are inserted as is or Insert...Values
where all are manual values. Is there a statement that can be a combination
of these two?
ThanksYou should be able to formulate an INSERT SELECT statement to do what you
want:
INSERT INTO TableB (col1, col2, col3, col4, ...)
SELECT colA, 'some value', 1234, colB, ...
FROM TableA
WHERE ...
--
David Portas
SQL Server MVP
--|||On Mon, 13 Dec 2004 15:01:01 -0800, Niles wrote:
>Can I do an insert from table A to table B only columns that are in A but not
>in B and changing them before insert? I want the primary key of rows in A
>that don't exist in B to be inserted in B, but I want to flag them in a
>couple of other columns. I see that I can either use an Insert query with
>select statement where all the columns are inserted as is or Insert...Values
>where all are manual values. Is there a statement that can be a combination
>of these two?
>Thanks
Hi Niles,
You mean something like this?
INSERT INTO TableB (KeyColumn, OtherColumn, ThirdColumn)
SELECT KeyColumn, OtherColumn, 'Literal value'
FROM TableA AS a
WHERE NOT EXISTS
(SELECT *
FROM TableB AS b
WHERE b.KeyColumn = a.KeyColumn)
or alternatively:
INSERT INTO TableB (KeyColumn, OtherColumn, ThirdColumn)
SELECT a.KeyColumn, a.OtherColumn, 'Literal value'
FROM TableA AS a
LEFT OUTER JOIN TableB AS b
ON b.KeyColumn = a.KeyColumn
WHERE b.KeyColumn IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)