| Author |
 Topic  |
|
|
learning_grsql
Posting Yak Master
155 Posts |
 Posted - 08/14/2012 : 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.
|
Edited by - learning_grsql on 08/14/2012 04:50:18
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/14/2012 : 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. |
Edited by - sunitabeck on 08/14/2012 08:00:54 |
 |
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 08/14/2012 : 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..
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/14/2012 : 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. |
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 08/14/2012 : 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. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
|
|
YuvarajKrishna
Starting Member
India
4 Posts |
Posted - 08/14/2012 : 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 |
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 08/14/2012 : 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. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/14/2012 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 08/14/2012 : 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/
|
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 08/14/2012 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 08/14/2012 : 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/
|
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 08/14/2012 : 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... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 08/14/2012 : 17:57:20
|
can you show your used trigger query?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 08/15/2012 : 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.
|
Edited by - learning_grsql on 08/15/2012 02:50:01 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/15/2012 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 08/15/2012 : 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/
|
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 08/15/2012 : 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? |
Edited by - learning_grsql on 08/15/2012 13:28:32 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 08/15/2012 : 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/
|
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 08/15/2012 : 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?
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/15/2012 : 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!! :) |
|
|
| |
Topic |
|
table trigger to add values from another table
