Insert Trigger and Updating a view

I am just getting back to SqlServer and TSQL after a 4 year hiatus.
I want to write a trigger to update a view with the same record that is
being inserted into a table. I have a trigger bound to the table to be
inserted and since it is a simple process, I will probably forgoing using a
stored proc.
In my trigger I want to essentially do:
On Insert....
Update MyView
Set Col A = NewCol A Value,
Col B = NewCol B Value,
Col C = NewCol C Value
The NewCol x Value values are the insert values of the record being posted
to the table being inserted.
Interbase has New property. Can anyone provide the syntac to accomplish my
task?
TIA
LarryLarry,
There are two special tables accessible within a trigger,
inserted and deleted. They hold the new rows (for inserts
and updates) and the old rows (for deletes and updates)
of the target table with respect to the statement that fired
the trigger. Note that a trigger fires only once, whether the
triggering statement affects multiple rows or not, and so the
inserted and deleted tables can have more than one row.
It sounds like your triggering statement will be affecting
only one row, but it is still a good idea to consider making
sure of that by checking @.@.rowcount at the very beginning
of the trigger.
Your trigger will probably look something like this:
create trigger... as
if @.@.rowcount <> 1 begin
raiserror (as appropriate)
rollback transaction -- or return, or whatever you need
update MyView set
ColA = i.ColA,
ColB = i.ColB,
. and so on
where MyView.viewKey = i.ColumnIdentifyingViewRowToUpdate
If you want, post CREATE TABLE statement and sample data for an
example and we can try to help more specifically to your case. You
can also find out more about the special tables inserted and deleted
in Books Online.
Steve Kass
Drew University
DelphiGuy wrote:

>I am just getting back to SqlServer and TSQL after a 4 year hiatus.
>I want to write a trigger to update a view with the same record that is
>being inserted into a table. I have a trigger bound to the table to be
>inserted and since it is a simple process, I will probably forgoing using a
>stored proc.
>In my trigger I want to essentially do:
>On Insert....
>Update MyView
>Set Col A = NewCol A Value,
> Col B = NewCol B Value,
> Col C = NewCol C Value
>
>The NewCol x Value values are the insert values of the record being posted
>to the table being inserted.
>Interbase has New property. Can anyone provide the syntac to accomplish my
>task?
>TIA
>Larry
>
>

Insert Statement

Hi Friends,
In my SP i have 3 insert statements that inserts record into 3 different
tables. If any of the inserts fail, I want to roll back any other inserts
that is executed. how to do this. please give me an example.
example:
insert into abc values('ert','ert')
insert into xyz values('rtert','rtyrty')
if the insert operation fails for the table xyz then the insert for abc
should not be commited.
thnks
vanithaput your sql statement in a transaction
begin transaction
insert 1....
insert 2....
insert 3.......
commit transaction
<hr>
MCP #2324787
"Vanitha" wrote:

> Hi Friends,
> In my SP i have 3 insert statements that inserts record into 3 different
> tables. If any of the inserts fail, I want to roll back any other inserts
> that is executed. how to do this. please give me an example.
> example:
> insert into abc values('ert','ert')
> insert into xyz values('rtert','rtyrty')
> if the insert operation fails for the table xyz then the insert for abc
> should not be commited.
> thnks
> vanitha|||CREATE PROCC dbo.ProcedureName
(paramlist)
AS
BEGIN
BEGIN TRAN [tranname]
INSERT INTO First table ...
IF @.@.ERROR > 0
BEGIN
ROLLBACK TRAN [tranname]
RETURN [Errorcode]
END
INSERT INTO second table ...
IF @.@.ERROR > 0
BEGIN
ROLLBACK TRAN [tranname]
RETURN [Errorcode]
END
INSERT INTO third table ...
IF @.@.ERROR > 0
BEGIN
ROLLBACK TRAN [tranname]
RETURN [Errorcode]
END
COMMIT TRAN [tranname]
END
GO
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:08B477A8-5335-45EB-BAC2-B76B2B2A5563@.microsoft.com...
> Hi Friends,
> In my SP i have 3 insert statements that inserts record into 3 different
> tables. If any of the inserts fail, I want to roll back any other inserts
> that is executed. how to do this. please give me an example.
> example:
> insert into abc values('ert','ert')
> insert into xyz values('rtert','rtyrty')
> if the insert operation fails for the table xyz then the insert for abc
> should not be commited.
> thnks
> vanitha|||This involves error handling as well as transaction handling. It is a large
topic, so some
background reading will sort this out for you. The best reference to this to
pic, IMO, is the error
handling articles at:
http://www.sommarskog.se/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:08B477A8-5335-45EB-BAC2-B76B2B2A5563@.microsoft.com...
> Hi Friends,
> In my SP i have 3 insert statements that inserts record into 3 different
> tables. If any of the inserts fail, I want to roll back any other inserts
> that is executed. how to do this. please give me an example.
> example:
> insert into abc values('ert','ert')
> insert into xyz values('rtert','rtyrty')
> if the insert operation fails for the table xyz then the insert for abc
> should not be commited.
> thnks
> vanitha|||Just adding BEGIN TRAN and COMMIT TRAN will not cut it. The first might be O
K, the second fail and
the third OK. So the first and the third are performed but not the second. Y
ou need error handling
as well (or SET XACT_ABORT ON, but almost no-one in the SQL Server community
uses this setting).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:80DA1215-F68E-4FA3-BE39-3B08BD39473A@.microsoft.com...
> put your sql statement in a transaction
>
> begin transaction
> insert 1....
> insert 2....
> insert 3.......
> commit transaction
>
> --
>
> <hr>
> MCP #2324787
>
> "Vanitha" wrote:
>|||Thanks
"Tibor Karaszi" wrote:

> This involves error handling as well as transaction handling. It is a larg
e topic, so some
> background reading will sort this out for you. The best reference to this
topic, IMO, is the error
> handling articles at:
> http://www.sommarskog.se/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
> news:08B477A8-5335-45EB-BAC2-B76B2B2A5563@.microsoft.com...
>

Insert row in table with Identity field, and get new Identity back

I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).

What is the most direct way to do this in SSIS?

TIA,

barkingdog

P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?

If you need to do this for every row, then using identities with an SSIS is not a good idea. You cannot get the new identity back until the row is committed, but that would mean committing one row at a time in SSIS. Even then, you only get back the last identity - which may not be what you expect if a parallel process has added a row between you commiting your row and asking for it's identity.

The best way is to use a script to generate a key in the data flow. In that way, you will know what the key value for each row is in advance and it can be inserted (thanks to multicast) into different tables at once, guaranteeing referential integrity.

Donald

|||

Donald,

When you wrote "You cannot get the new identity back until the row is committed, but that would mean committing one row at a time in SSIS."

When I run a normal SSIS package that reads from a file and writse to a database isn't one row being committed at a time? Or does SSIS save as many rows as possible in, say a memory buffer, and then commit then all at once?

TIA,

barkindog

|||

Strictly speaking it is the provider that handles commits, not SSIS.

The Fastload option on the OLEDB provider allows you to set batch sizes from 1 to "the entire data load in one batch."

If you do not use Fast Load, then one row at a time is sent.

The OLEDB command component also processes one row at a time.

However, in all these cases, the problem is not the performance of handling one row at a time (although that is a real factor) - it is also that you cannot get back the identity for the row you have just committed.

The pattern in SQL Server (and in most rdbms's) is that you can get the last identity issued. It is tempting to think that having just posted a row, the last identity issued must be for that row. Many a design has foundered on that assumption, as just the teensiest smidgin of parallelism soon throws that process out of synchronization.

I much prefer issuing keys in advance in the ETL process - you can do so much with them, with great performance and guaranteed integrity.

Donald

|||

Regarding "Many a design has foundered on that assumption, as just the teensiest smidgin of parallelism soon throws that process out of synchronization."

1. If my job is the only one updating the table with the Identity column , and I'm not running multiple copies of my job, then I presume that parallellism can't happen to me. Or does SSIS do things "in the background" that could cause a smidgin of parallelism, even for my particular case?

2. Later on I will need to re-run my job with new data. Then I have to read the current value of the Identity from the table, add 1 to it, and begin with that value. Your argument about parallelism makes me wonder if the only way to accurately read the identity value from a table is to make sure no other app updates that table. (That sure puts a dent in the possibility of scaling out horizontally with servers.)

TIA,

barkingdog

|||

1. The OLEDB command destination may send a command for the second row before the first has completed. Our buffer architecture is designed to maximise the potential for pipeline parallelism.

2. The only way to guarantee that the last identity you read is the last one you inserted, is to be able to guarantee that no process has written to the table since your process.

We do have a design pattern for highly parallel key generation that may (but may not) be in the next version . Either way there will be a paper on this at some point.

The best strategy is to know your keys in advance - by generating them in your data integration process. That way, you have complete control.

Donald

insert record from another database

hi. we have 1 production database and a 2nd backup database. one of our users accidentally deleted a case and we would like to get it back. our production database is updated nightly so none of the case's information would've changed.

how can i insert the data from the backup database to the production database? i need to copy one row. thanks for your help!just one row?

how about a simple insert statement?