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
 General SQL Server Forums
 New to SQL Server Programming
 table trigger to add values from another table

Author  Topic 

learning_grsql
Posting Yak Master

230 Posts
Posted - 2012-08-14 : 04:44:40
I have maintable table like this. There is no data for columns owner and newowner

id | _____date | item | color | quantity | owner | newowner
1 | 1 July 2012 | pen G | yellow | 10
2 | 1 July 2012 | gum A | white | 25
3 | 2 July 2012 | stick G | green | 36


The other table is like this
item | owner | new owner
pen G | Tim | Tim & Tom
gum A | Vaun | Vaun & Co.
stick G | kane | kane & Co.

Now I want to inner join the both tables on column item and place owner and [new owner] values from the other table into maintable using table trigger.

In fact the maintable data is imported from excel file. So whenever data is imported or inserted into maintable, it should automatically add values to owner and [new owner columns] using trigger.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-08-14 : 07:02:15
You can write a trigger like this to do what you are trying to do:
CREATE TRIGGER dbo.YourTablesUpdateTrigger ON dbo.YourTable
FOR INSERT
AS
BEGIN
	UPDATE m SET
		m.owner = t.owner,
		m.newowner = t.newowner
	FROM
		INSERTED i
		INNER JOIN OtherTable t ON t.item = i.item
		INNER JOIN Maintable m ON m.item = i.item;
END
I have a few comments though:

a) I usually prefer to keep business logic out of triggers. I use triggers only when absolutely necessary - for example for auditing. If I were tasked with this, I would import the data from the Excel file into a staging table and have another step that copies the data from the staging table along with the correct values of owner and newower into the maintable.

b) The code I posted above will update every row that has the items that are inserted, even if they were rows inserted previously.

c) Depending on how you are importing the Excel file, triggers may not fire. You would need to take care of that.
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts
Posted - 2012-08-14 : 07:41:52
Thanks sunita
i tried your code but it is not triggering...0 rows affected when i execute...even i tried importing new records...it did not update in new records also..
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-08-14 : 08:02:39
I was using the wrong aliases in the original post - made changes see in red above. (I am surprised it didn't generate an error)

How are you inserting the data? If you are using SSIS, the default options may not be causing the trigger to fire.
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts
Posted - 2012-08-14 : 08:12:58
aliases i had already corrected.

yes im using SSIS for importing. But according to you it should fire when we execute for rows already in the table at least, right? But it didn't. neither for new rows nor for newly imported rows.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-08-14 : 08:55:00
I think triggers may not fire by default. See Ranjeeta Nanda's response in this thread. You need to add FIRE_TRIGGERS to the FastLoadOptions as he is describing.

Edit: Forgot to insert the link: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/e2464b32-7750-4901-82e2-d349c365ee6b/
Go to Top of Page

YuvarajKrishna
Starting Member

4 Posts
Posted - 2012-08-14 : 09:09:43
Try This One
CREATE TRIGGER TriggerForUpdate ON OtherTable
for INSERT
AS
BEGIN
UPDATE A SET
A.owner = B.owner,
A.newowner = B.newowner
FROM maintable A INNER JOIN OtherTable B ON A.item = B.item
WHERE (A.Owner IS NULL OR A.Owner = '')
END

Yuvaraj
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts
Posted - 2012-08-14 : 09:53:52
@sunita,
where i can find the fastloadoptions...i opened "business intelligence management studio" and then file > new project. After that I'm not sure where to locate the option. Can you guide me ?

Many thanks for all your replies.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-08-14 : 10:00:10
If you already have a package, make a backup copy of that and then drag it into BIDS or SSMS. Then, in the Dataflow tab, right-click on the Destination block, select Show Advanced Editor and select Component Properties. The FastLoadOptions should be in that screen.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-14 : 10:23:53
quote:
Originally posted by YuvarajKrishna

Try This One
CREATE TRIGGER TriggerForUpdate ON OtherTable
for INSERT
AS
BEGIN
UPDATE A SET
A.owner = B.owner,
A.newowner = B.newowner
FROM maintable A INNER JOIN OtherTable B ON A.item = B.item
WHERE (A.Owner IS NULL OR A.Owner = '')
END

Yuvaraj

logic is not correct
this trigger will end up in updating entire records in maintable always for each insert action on othertable

you should be using internal table DELETED. refer logic used by Sunita

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts
Posted - 2012-08-14 : 16:07:54
quote:
Originally posted by sunitabeck

If you already have a package, make a backup copy of that and then drag it into BIDS or SSMS. Then, in the Dataflow tab, right-click on the Destination block, select Show Advanced Editor and select Component Properties. The FastLoadOptions should be in that screen.


I'm not sure what you mean by "package". I never heard about it before.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-14 : 16:56:19
quote:
Originally posted by learning_grsql

quote:
Originally posted by sunitabeck

If you already have a package, make a backup copy of that and then drag it into BIDS or SSMS. Then, in the Dataflow tab, right-click on the Destination block, select Show Advanced Editor and select Component Properties. The FastLoadOptions should be in that screen.


I'm not sure what you mean by "package". I never heard about it before.

Package refers to SSIS components you create using BIDS under Integration Services project

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts
Posted - 2012-08-14 : 17:52:56
quote:
Originally posted by sunitabeck

If you already have a package, make a backup copy of that and then drag it into BIDS or SSMS. Then, in the Dataflow tab, right-click on the Destination block, select Show Advanced Editor and select Component Properties. The FastLoadOptions should be in that screen.

Thanks Sunita, Visakh16 and Yuvaraj
Finally I managed to locate the option fastload and even I added "FIRE_TRIGGERS" as given in the link but still it's not firing when i execute or add new data...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-14 : 17:57:20
can you show your used trigger query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts
Posted - 2012-08-15 : 02:46:31
i used the same code provided by Sunita as below 

REATE TRIGGER dbo.YourTablesUpdateTrigger ON dbo.YourTable
FOR INSERT
AS
BEGIN
	UPDATE m SET
		m.owner = t.owner,
		m.newowner = t.newowner
	FROM
		INSERTED i
		INNER JOIN OtherTable t ON t.item = i.item
		INNER JOIN Maintable m ON m.item = i.item;
END

I think i might have made mistake in SSIS package.

I will let you know the procedure I followed.

First, i deleted all data from maintable.(only data)

Then I again imported data using "import and export wizard". During the last step of importing, there was an option "save SSIS Package". I used the option to save package.

Then opened BDIS and clicked new project >> and then integration service project >>

Then at right side of the window, I right clicked "SSIS package" and selected "Existing Package". I executed the package.

It automatically opened in data flow with source block and destination block. I right clicked destination block and as advised went to fastload option and added an option "Fire_Triggers".

I tried executing the trigger, but it still didn't update the columns for already existing data. I tried importing new data to the maintable using the same import and export wizard again. It didn't update the columns still.

I have a doubt here. I feel maybe I'm making mistakes here.

Shall we still use "import and export wizard" to import data after we save them in package?

Or shall we need to just open the package and import the data? I don't find such an option though.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-08-15 : 07:03:03
All of that seems fine - not sure what might be wrong. I will take another look at it; hopefully someone else who has better insights might offer suggestions as well.

You are doing it right as far as I can tell - i.e., created the SSIS package and edited it to add the FIRE_TRIGGERS option, saved it and then ran it. Once you save it, it shouldn't matter where you run it from BIDS, or the package directly.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-15 : 10:25:44
I tried importing new data to the maintable using the same import and export wizard again. It didn't update the columns still

you mean you still launched a new export import wizard even after doing changes to package? you should be running package rather than using export/import wizard after doing the changes.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts
Posted - 2012-08-15 : 13:28:00
quote:
Originally posted by visakh16

I tried importing new data to the maintable using the same import and export wizard again. It didn't update the columns still

you mean you still launched a new export import wizard even after doing changes to package? you should be running package rather than using export/import wizard after doing the changes.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Thanks visakh
Yes you are correct. I launched a new export import wizard even after doing changes to package.
I don't know how to run the existing package to import new data. Can you help with this?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-15 : 13:33:12
quote:
Originally posted by learning_grsql

quote:
Originally posted by visakh16

I tried importing new data to the maintable using the same import and export wizard again. It didn't update the columns still

you mean you still launched a new export import wizard even after doing changes to package? you should be running package rather than using export/import wizard after doing the changes.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Thanks visakh
Yes you are correct. I launched a new export import wizard even after doing changes to package.
I don't know how to run the existing package to import new data. Can you help with this?

Open BIDS and start an integration services project. include the saved package in it and execute it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts
Posted - 2012-08-15 : 16:02:20
quote:
Originally posted by visakh16

quote:
Originally posted by learning_grsql

quote:
Originally posted by visakh16

I tried importing new data to the maintable using the same import and export wizard again. It didn't update the columns still

you mean you still launched a new export import wizard even after doing changes to package? you should be running package rather than using export/import wizard after doing the changes.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Thanks visakh
Yes you are correct. I launched a new export import wizard even after doing changes to package.
I don't know how to run the existing package to import new data. Can you help with this?

Open BIDS and start an integration services project. include the saved package in it and execute it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



wow! Thanks a lot Visakh and Sunita. Trigger working now after I executed from package.

So basically if I want to use Table trigger, i must create package and run from it? otherwise trigger will not work. Is it correct?
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-08-15 : 17:21:05
quote:
So basically if I want to use Table trigger, i must create package and run from it? otherwise trigger will not work. Is it correct?
Yes - only because, by default, if you use Import/Export wizard, by default, the FIRE_TRIGGERS option is turned off. There might be some option or setting that may allow you to turn it on by default, but I don't know where/what that is.

You can create it in SSMS or BIDS, and run it from there as long as you have turned on the FIRE_TRIGGERS option.

Glad you got it figured out!! :)
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site