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 Table Lock - Various connections inserting into one table

I have three connections from oracle DBs which all insert into 1 SQL
Server 2000 table. About 1 million rows in all. The problem is only one
connection is inserting at one time... then the second... then
third.... The first connection obtains a table lock causing other
connections to wait until it completes...should'nt they run
simultaneously'...My DBA recently changed the from FULL to Simple
recovery model... does that has anything to do with how insert table
locks are being handled' If I was on simple recovery model....
would I be able to insert into one table from three different
connections or SQL statements?Hi Zomer
The recovery model should not affect the locking.
How are you determining that a table lock is being acquired?
Are the connections doing more than inserts? Are they starting an explicit
transaction? (Look for BEGIN TRAN)
What isolation level are you in? Have you enabled implicit transactions?
(Run DBCC USEROPTIONS on the connection)
Have you disallowed page and row locks (check indexproperty function)
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"zomer" <noneee@.gmail.com> wrote in message
news:1146586395.557732.192510@.u72g2000cwu.googlegroups.com...
>I have three connections from oracle DBs which all insert into 1 SQL
> Server 2000 table. About 1 million rows in all. The problem is only one
> connection is inserting at one time... then the second... then
> third.... The first connection obtains a table lock causing other
> connections to wait until it completes...should'nt they run
> simultaneously'...My DBA recently changed the from FULL to Simple
> recovery model... does that has anything to do with how insert table
> locks are being handled' If I was on simple recovery model....
> would I be able to insert into one table from three different
> connections or SQL statements?
>|||I run sp_lock to find out if a table lock was acquired or not. I am
doing it in DTS and I can see one connections getting data while others
are waiting for it to get done (due to table lock)
How do I check for Transaction isolation level? When I run DBCC
USEROPTIONS I dont see any informaton about transactions...
No I have not disallowed page and row locks...
A few days ago this DB was changed to simple from full recovery model
by DBA|||If DBCC USEROPTIONS doesn't mention transaction isolation, it means you
haven't changed it from the default, but you have to be running it from the
connection doing the insert.
Did you run sp_indexoption to verify that page and row locks are allowed?
Can you post the specific information from sp_lock that is showing the table
lock?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"zomer" <noneee@.gmail.com> wrote in message
news:1146590297.747832.125830@.i39g2000cwa.googlegroups.com...
>I run sp_lock to find out if a table lock was acquired or not. I am
> doing it in DTS and I can see one connections getting data while others
> are waiting for it to get done (due to table lock)
> How do I check for Transaction isolation level? When I run DBCC
> USEROPTIONS I dont see any informaton about transactions...
> No I have not disallowed page and row locks...
> A few days ago this DB was changed to simple from full recovery model
> by DBA
>