Insert through a trigger. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert through a trigger.

Author

Topic

mioara
Starting Member

3 Posts

Posted - 2008-01-31 : 04:50:20

Hi !
I have the following problem : I wrote a trigger AFTER INSERT into a table x .
I want that this trigger to update 3 fields from the table x with values taken from other tables. How can I achieve this ?

I wrote something like that (x=IndexeOrare), but the UPDATE blocks the execution :

ALTER TRIGGER [dbo].[CompletareIndexeOrare]
ON [dbo].[IndexeOrare]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SerieContor Varchar(20)
DECLARE @CodConsumator Varchar(10)
DECLARE @NumarContract Varchar(10)
DECLARE @CodLocConsum Varchar(10)

SELECT @SerieContor=(SELECT SerieContor FROM Inserted)

DECLARE CompletariCursor CURSOR FAST_FORWARD FOR
SELECT pm.CodLocConsum,lc.CodConsumator,lc.NumarContract
FROM PuncteMasura pm LEFT OUTER JOIN LocuriConsum lc
ON pm.CodLocConsum=lc.CodLocConsum
WHERE pm.SerieContorActiv=@SerieContor

OPEN CompletariCursor
FETCH NEXT FROM CompletariCursor INTO @CodLocConsum ,@CodConsumator,@NumarContract
WHILE @@FETCH_STATUS=0

BEGIN

BEGIN
UPDATE IndexeOrare SET CodConsumator=@CodConsumator,NumarContract=@NumarContract,
CodLocConsum=@CodLocConsum

END
FETCH NEXT FROM CompletariCursor INTO @CodLocConsum,@CodConsumator,@NumarContract

END

CLOSE CompletariCursor
DEALLOCATE CompletariCursor

END

Thanks,
Mioara

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-31 : 05:05:38

Can you explain your requirement in more detail with some sample data and table structure?

mioara
Starting Member

3 Posts

Posted - 2008-01-31 : 07:18:29

Let's put it this way :

I have 2 tables :

Customers
with 2 fields :
- CustomerID
- CustomerName

Orders
with 4 fields :
- OrderID
- CustomerID
- CustomerName
- ProductID

I have a SSIS that fills the following 3 fields in my Orders table (the package takes the data
from another database) :

- OrderID
- CustomerName
- ProductID.

I want to make a trigger for INSERT ( for Orders table) , that takes the field CustomerID
from Customers table and fills the field CustomerID from Orders table.

How can I write this ?

Thanks again

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2008-01-31 : 09:16:44

quote:
I want to make a trigger for INSERT ( for Orders table) , that takes the field CustomerID
from Customers table and fills the field CustomerID from Orders table.

Based on what? CustomerName? (BTW Since this field is dependent upon CustomerID, you should remove this from your Orders table).
Based on your second post, it looks as though you should just be performing a lookup to the Customer table based on the CustomerName field in your dataflow (I'm assuming that this is unique?), to return CustomerID. Without going into the code in your first post in any great detail, I'd suggest that if you find yourself executing cursors within triggers, there's probably a better alternative...

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources