Inserted Table when Inserting

Hi,

Now thanks to you good folks on here, I have recently found out that when inserting data into table, there is a system table which can be queried with triggers - specifically called "Inserted".

What I am wondering is what are the limitations of what I can do with the data in this table?
I know I can query it from within the trigger, but can I update data specifically in this table before it is inserted?
(ie IF field1 FROM inserted = 'blah' UPDATE inserted SET field2 = 'something')

If so is there anything that I need to look out for? Concerns? Etc?

Thanks in advance for your help

Cheersyou can use the data for comparisons or you can join the query argument in the trigger to the inserted and or the deleted tables

I have never updated them directly so i cant speak to that but i can suggest that anything that you might want to change in these virtual tables (Inserted\Deleted) could just as easily be changed in the triggered or evaluated table directly from the trigger code.

remember these tables contain data to give you a before and after look at the transaction that the trigger is a part of
(a trigger is implicitly part of the X-act that calls it)
so they dont technically exist when you are not in a X-act|||It is a very bad idea to try to modify either INSERTED or DELETED directly, they are implemented in "curious" ways. While you might be able to update them, it is still a very bad idea to do it.

-PatP|||Ahh haaa so if I understand correctly - what you are basically saying is that the information contained in this table, is ALREADY inserted into the table.
So if the file I was inserting had a PK field = 1234, and I wanted to update something in this file once it was inserted I could say something to the effect of:

update table1
set field1 = blah
from table1
where table1.field2 = inserted.field2

Rather than:

update inserted
set field1 = blah
from inserted

Hmm hopefully I have made a bit of sense here....

Thanks.|||Originally posted by Pat Phelan
It is a very bad idea to try to modify either INSERTED or DELETED directly, they are implemented in "curious" ways. While you might be able to update them, it is still a very bad idea to do it.

-PatP

The logical tables INSERTED and DELETED cannot be updated.|||E3xtc

yes that is the case
basically when you perform an insert on a table that has a trigger on it (for insert)
1 the row is inserted to the table
2 the row is also added into the "inserted" table
(which is only available to the xact that calls it)
3 the trigger actions are executed
4 commit or rollback

for deleted the same actions occur except the row to be deleted is added to the deleted table.

an update (in some cases) is a insert and a delete so there is no actual "updated" table
on an update the row as it existed before the update is added to the "deleted" table and the row with the updated column is added to the "inserted" table.

while the table exists(during trigger execution) you can query it just as you would any table.|||Originally posted by E3xtc
Ahh haaa so if I understand correctly - what you are basically saying is that the information contained in this table, is ALREADY inserted into the table. Yep, that you did understand that correctly!

The rows are modified first, placed in a pair of "non-corporeal" tables named INSERTED and DELETED. These tables can be freely modified in an INSTEAD OF trigger if the database compatibility level is set to 80. In the first releases of sp1 and sp3, and in several PSS hot fixes you could update the INSERTED and DELETED tables in any kind of trigger, with any database compatibility level. It is still a bad idea!

In general, it is considered "good form" to use a JOIN back to the primary (host) table to change the values of columns. This becomes much more important in the 64 bit version of SQL 2000, and will be even more so in Yukon.

-PatP|||brilliant!! Thanks all for your help - it is crystal clear now.

Much appreciated!

INSERT,INSERT then UPDATE

Hi all,
I'm making a system in which I store accounts and addresses.
To make the database atomic this info is in different tables because one
account may have many addresses (invoice address, several delivery
addresses, etc). I have put the account as having a primary address stored
in the accounts table. I also tag the account ID onto every address so I can
quickly pull up all the addresses for a particular account.
My problem is.. When I insert a new account I have to insert the address
first missing the account ID (because there isn't one yet), doing this
returns the identity of the address. I can then insert the account with the
primary addresses identity... and now I have inserted the account I can
return the identity of the account to update the address to include the
account id.
Basically my question is... is there an easier way? It seems a bit long
winded.
Thanks
Gav> My problem is.. When I insert a new account I have to insert the address
> first missing the account ID (because there isn't one yet), doing this
> returns the identity of the address.
Can you explain why you have to do it in this order?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||I guess it doesn't really matter which way around I do it, my problem is I
need information in both tables that I don't get until I insert it. I could
just as will insert the account returning the identity then insert the
address whith the account id. returning the address id which i would then
have to update the accounts table to set the identity of the primary
address.
This is done in three steps it could be done in four if i inserted both
records first getting the identity of each, then updating them both to set
the foreign key.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OW2EWsX8DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > My problem is.. When I insert a new account I have to insert the address
> > first missing the account ID (because there isn't one yet), doing this
> > returns the identity of the address.
> Can you explain why you have to do it in this order?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>|||Hi Gav,
Thank you for using the newsgroup.
As my understanding of your question, you have two tables, one will stored
the information as
AccountID, Account, PrimaryAddress, you have another table address
AccountID OtherAddress, and you have some problem of saving the data, since
there maybe an account without AccountID, etc.
What I would suggest is that, could you save all the information in one
address table' That would be a table includ:
RID, AccountID, Account, Address, Addresstype
Then you also could update the address informaiton according to the
AccountID or the Account.
Hope this helps. If you still have any questions, please feel free to post
message here and I am ready to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I was hoping that what I was after could be done in one stored procedure...
you see I'm using .NET calls to run the procedures but instead of running
three I would like to run just one.
I'm only used to doing simple statments in the stored procedures. But surely
I can do what I want using a stored procedure.. I guess it would go
something like this:
INSERT INTO ACCOUNTS (AccountName, PrimaryAddress) Values (@.AccountName,
[INSERT INTO ADDRESSES (Address1, address2) VALUES (@.Add1,@.Add2) SELECT
@.@.IDENTITY*]) SELECT @.@.IDENTITY
UPDATE ADDRESSES SET AccountID = @.@.IDENTITY WHERE '?
I'm unsure about this *would this return the new identity for the record and
put it in PrimaryAddress for Accounts? Is there a better way to use
variables (can I store the first return to use in the WHERE parameter on the
second statement)? Like a programming statement will it execute the code is
[] brackets first?
Obviously I'm trying to move the processes away from the code and into the
SQL Procedure if I can I believe it would speed things up slightly.
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:n2E56Cg8DHA.3472@.cpmsftngxa07.phx.gbl...
> Hi Gav,
> Thank you for using the newsgroup.
> As my understanding of your question, you have two tables, one will stored
> the information as
> AccountID, Account, PrimaryAddress, you have another table address
> AccountID OtherAddress, and you have some problem of saving the data,
since
> there maybe an account without AccountID, etc.
> What I would suggest is that, could you save all the information in one
> address table' That would be a table includ:
> RID, AccountID, Account, Address, Addresstype
> Then you also could update the address informaiton according to the
> AccountID or the Account.
> Hope this helps. If you still have any questions, please feel free to post
> message here and I am ready to help.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Gav,
Thank you for your update.
There is no machenism in T-SQL as you described as [ ]. For a relational
database, you should keep the data integrety. So, how about to create a
table as I suggested in my previous post: As you may have a AccountName
without a AccountID, you you need to access its information by AccountName.
How about o keep these in formation in one record:
ID, AccountID, AccountName, Address, AddressContent, AddressType
The AddressType will indicate if it is a primary address or other address
type?
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||this would force me to repeat the account name for each address making the
database bad design. + where would it get the account ID from.
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:Ma9QWOu8DHA.2164@.cpmsftngxa07.phx.gbl...
> Hi Gav,
> Thank you for your update.
> There is no machenism in T-SQL as you described as [ ]. For a relational
> database, you should keep the data integrety. So, how about to create a
> table as I suggested in my previous post: As you may have a AccountName
> without a AccountID, you you need to access its information by
AccountName.
> How about o keep these in formation in one record:
> ID, AccountID, AccountName, Address, AddressContent, AddressType
> The AddressType will indicate if it is a primary address or other address
> type?
> Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||> need information in both tables that I don't get until I insert it. I
could
> just as will insert the account returning the identity then insert the
> address whith the account id. returning the address id which i would then
> have to update the accounts table to set the identity of the primary
> address.
I don't understand this at all. Can you tell me why this kind of scenario
doesn't work? Paste into Query Analyzer (on a scratch db) and run it...
CREATE TABLE Accounts
(
AccountID INT IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
AccountName VARCHAR(32)
--, etc etc
)
GO
SET NOCOUNT ON
CREATE TABLE Addresses
(
AccountID INT FOREIGN KEY
REFERENCES Accounts(AccountID),
Address VARCHAR(64), -- ?
AddressType INT
)
GO
DECLARE @.AccountID INT
-- create an account for Bob, who has a
-- primary and secondary address
INSERT Accounts(AccountName) VALUES('Bob')
SET @.AccountID = SCOPE_IDENTITY()
INSERT Addresses(AccountID, Address, AddressType)
SELECT @.AccountID, '1 Test St.', 1
INSERT Addresses(AccountID, Address, AddressType)
SELECT @.AccountID, '2 Test St.', 2
-- now, create an account for Frank,
-- who only has a primary address
INSERT Accounts(AccountName) VALUES('Frank')
SET @.AccountID = SCOPE_IDENTITY()
INSERT Addresses(AccountID, Address, AddressType)
SELECT @.AccountID, '3 Test St.', 1
-- see all of the accounts and addresses
SELECT * FROM Accounts ac
INNER JOIN Addresses ad
ON ac.AccountID = ad.AccountID
-- see only the primary addresses of
-- each account
SELECT * FROM Accounts ac
INNER JOIN Addresses ad
ON ac.AccountID = ad.AccountID
AND ad.AddressType = 1
-- you *could* add a constraint or
-- trigger to prevent an account from
-- having more than one primary add.
GO
DROP TABLE Addresses
DROP TABLE Accounts
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

INSERT,INSERT then UPDATE

Hi all,
I'm making a system in which I store accounts and addresses.
To make the database atomic this info is in different tables because one
account may have many addresses (invoice address, several delivery
addresses, etc). I have put the account as having a primary address stored
in the accounts table. I also tag the account ID onto every address so I can
quickly pull up all the addresses for a particular account.
My problem is.. When I insert a new account I have to insert the address
first missing the account ID (because there isn't one yet), doing this
returns the identity of the address. I can then insert the account with the
primary addresses identity... and now I have inserted the account I can
return the identity of the account to update the address to include the
account id.
Basically my question is... is there an easier way? It seems a bit long
winded.
Thanks
Gav> My problem is.. When I insert a new account I have to insert the address
> first missing the account ID (because there isn't one yet), doing this
> returns the identity of the address.
Can you explain why you have to do it in this order?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||I guess it doesn't really matter which way around I do it, my problem is I
need information in both tables that I don't get until I insert it. I could
just as will insert the account returning the identity then insert the
address whith the account id. returning the address id which i would then
have to update the accounts table to set the identity of the primary
address.
This is done in three steps it could be done in four if i inserted both
records first getting the identity of each, then updating them both to set
the foreign key.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OW2EWsX8DHA.1804@.TK2MSFTNGP12.phx.gbl...
> Can you explain why you have to do it in this order?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>|||Hi Gav,
Thank you for using the newsgroup.
As my understanding of your question, you have two tables, one will stored
the information as
AccountID, Account, PrimaryAddress, you have another table address
AccountID OtherAddress, and you have some problem of saving the data, since
there maybe an account without AccountID, etc.
What I would suggest is that, could you save all the information in one
address table' That would be a table includ:
RID, AccountID, Account, Address, Addresstype
Then you also could update the address informaiton according to the
AccountID or the Account.
Hope this helps. If you still have any questions, please feel free to post
message here and I am ready to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I was hoping that what I was after could be done in one stored procedure...
you see I'm using .NET calls to run the procedures but instead of running
three I would like to run just one.
I'm only used to doing simple statments in the stored procedures. But surely
I can do what I want using a stored procedure.. I guess it would go
something like this:
INSERT INTO ACCOUNTS (AccountName, PrimaryAddress) Values (@.AccountName,
[INSERT INTO ADDRESSES (Address1, address2) VALUES (@.Add1,@.Add2) SELECT
@.@.IDENTITY*]) SELECT @.@.IDENTITY
UPDATE ADDRESSES SET AccountID = @.@.IDENTITY WHERE '?
I'm unsure about this *would this return the new identity for the record and
put it in PrimaryAddress for Accounts? Is there a better way to use
variables (can I store the first return to use in the WHERE parameter on the
second statement)? Like a programming statement will it execute the code is
[] brackets first?
Obviously I'm trying to move the processes away from the code and into the
SQL Procedure if I can I believe it would speed things up slightly.
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:n2E56Cg8DHA.3472@.cpmsftngxa07.phx.gbl...
> Hi Gav,
> Thank you for using the newsgroup.
> As my understanding of your question, you have two tables, one will stored
> the information as
> AccountID, Account, PrimaryAddress, you have another table address
> AccountID OtherAddress, and you have some problem of saving the data,
since
> there maybe an account without AccountID, etc.
> What I would suggest is that, could you save all the information in one
> address table' That would be a table includ:
> RID, AccountID, Account, Address, Addresstype
> Then you also could update the address informaiton according to the
> AccountID or the Account.
> Hope this helps. If you still have any questions, please feel free to post
> message here and I am ready to help.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Gav,
Thank you for your update.
There is no machenism in T-SQL as you described as [ ]. For a relational
database, you should keep the data integrety. So, how about to create a
table as I suggested in my previous post: As you may have a AccountName
without a AccountID, you you need to access its information by AccountName.
How about o keep these in formation in one record:
ID, AccountID, AccountName, Address, AddressContent, AddressType
The AddressType will indicate if it is a primary address or other address
type?
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||this would force me to repeat the account name for each address making the
database bad design. + where would it get the account ID from.
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:Ma9QWOu8DHA.2164@.cpmsftngxa07.phx.gbl...
> Hi Gav,
> Thank you for your update.
> There is no machenism in T-SQL as you described as [ ]. For a relational
> database, you should keep the data integrety. So, how about to create a
> table as I suggested in my previous post: As you may have a AccountName
> without a AccountID, you you need to access its information by
AccountName.
> How about o keep these in formation in one record:
> ID, AccountID, AccountName, Address, AddressContent, AddressType
> The AddressType will indicate if it is a primary address or other address
> type?
> Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||> need information in both tables that I don't get until I insert it. I
could
> just as will insert the account returning the identity then insert the
> address whith the account id. returning the address id which i would then
> have to update the accounts table to set the identity of the primary
> address.
I don't understand this at all. Can you tell me why this kind of scenario
doesn't work? Paste into Query Analyzer (on a scratch db) and run it...
CREATE TABLE Accounts
(
AccountID INT IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
AccountName VARCHAR(32)
--, etc etc
)
GO
SET NOCOUNT ON
CREATE TABLE Addresses
(
AccountID INT FOREIGN KEY
REFERENCES Accounts(AccountID),
Address VARCHAR(64), -- ?
AddressType INT
)
GO
DECLARE @.AccountID INT
-- create an account for Bob, who has a
-- primary and secondary address
INSERT Accounts(AccountName) VALUES('Bob')
SET @.AccountID = SCOPE_IDENTITY()
INSERT Addresses(AccountID, Address, AddressType)
SELECT @.AccountID, '1 Test St.', 1
INSERT Addresses(AccountID, Address, AddressType)
SELECT @.AccountID, '2 Test St.', 2
-- now, create an account for Frank,
-- who only has a primary address
INSERT Accounts(AccountName) VALUES('Frank')
SET @.AccountID = SCOPE_IDENTITY()
INSERT Addresses(AccountID, Address, AddressType)
SELECT @.AccountID, '3 Test St.', 1
-- see all of the accounts and addresses
SELECT * FROM Accounts ac
INNER JOIN Addresses ad
ON ac.AccountID = ad.AccountID
-- see only the primary addresses of
-- each account
SELECT * FROM Accounts ac
INNER JOIN Addresses ad
ON ac.AccountID = ad.AccountID
AND ad.AddressType = 1
-- you *could* add a constraint or
-- trigger to prevent an account from
-- having more than one primary add.
GO
DROP TABLE Addresses
DROP TABLE Accounts
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

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 system time in database

Dear Friends,

I want to insert in my database, in a table field the system time value on that moment.

For example: I want to create the follow stored procedure:

CREATE PROCEDURE TEST

@.ID INT

AS

UPDATE TABLE1 SET MyFieldTime=@.MySystemTime WHERE MyFieldID=@.ID

I want to save in my database th system time...

Thanks!!

UPDATE TABLE1 SET MyFieldTime=getdate() WHERE MyFieldID=@.ID|||

it always good practice to use the UTC time instead System's local time. In future if you transfer the data from one server (time zone) to another you need not to applay any changes..

Another benifit on UI you can convert to any Local time from UTC with out any overhead...

use the following query..

Update Table1 Set MyFiedlTime = GetUTCDate() Where MyFieldId = @.ID

|||

You should do this using a DEFAULT on the column and then use DEFAULT keyword in the SET clause of whatever UPDATE statement that modifies the data. Using a separate SP is not really a good idea since you will decouple the actual update and the time when it was done. It is also costly to perform multiple updates on the same row when you can do it once.

You can make below changes:

alter table TABLE1 add default( CURRENT_TIMESTAMP ) for MyFieldTime

After that when you do the actual UPDATE then do:

UPDATE TABLE1

SET col1 = ...

, col2 = ...

, MyFieldTime = DEFAULT

WHERE ...