Can someone help me with this scenario:
I have 2 tables one detail and one summary. The key fields for betwen the the detail and summary tables are the date field na the part # field.
I need to create a trigger that will insert records when they do not exist in the summary table and also only update the records that are needing to be modified. Any Ideas?
ThanksReal time warehousing?
Can't you run a scheduled process instead?|||Originally posted by Brett Kaiser
Real time warehousing?
Can't you run a scheduled process instead?
I need to have this summary infomration available at any time the daily reports need to be run. A scheduled process would be easier but I need to keep this table up to date as transcations are processed in the detail table.|||Use this as an example:
create table item(id int identity,item# varchar(10))
create table itemsummary(id int identity,item# varchar(10),quantity int)
go
create trigger iu_item on item
for insert,update
as
insert itemsummary(item#)
select item# from inserted i
where not exists(select 1 from itemsummary where item#=i.item#)
update itemsummary set quantity=(select count(*) from item i where i.item#=itemsummary.item#)
go
insert item(item#) values('#1')
insert item(item#) values('#1')
insert item(item#) values('#2')
select * from item
select * from itemsummary|||He'll need to update..the count? for the part#
So you need 2 sections...
Check
-- An Update
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
--An INSERT
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
Then do your apporpriate action...
What's the transaction level?sql
Showing posts with label scenario. Show all posts
Showing posts with label scenario. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
Insert Trigger
I am facing problem in creating a insert trigger for the following scenario.
i have transactions, control tables
whenever i insert a record in transactions it should get value from the control table, increment that value in control table and update the same value as transaction_id for new transaction in transaction table.
control table has these fields (control_desc, control_value)
can some one help me to write a trigger (insert trigger) in transactions table.
Tanks for the Help
Coudl you please provide more information like DDL code, showing which column you want to update etc.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for the replay
Fields in Transactions Table : trans_id,trans_type,amount,trans_date,uid
Fields in Control Table: id, id_value
I am inserting all the fields in transction table except trans_id when insert trigger fires i want to take id_value from control table which id is "trans_id" in id coulmn update the same in the trans_id field in transactiontable.
Can u help me reating this trigger.
Regards
|||Try this
--<Run Once>
drop table Transactions
drop table control
go
create table control
(
control_desc varchar(5) not null primary key,
control_value int not null
)
create table Transactions
(
trans_id int not null identity,
control_desc varchar(5) not null foreign key references control(control_desc),
control_value int not null
)
go
create trigger ti_Transactions on Transactions for insert
as
set nocount on
update c
set c.control_value = c.control_value + 1
from control c
join inserted i
on i.control_desc = c.control_desc
go
insert control select 'ABCDE', 10001
go
--</Run Once>
--<Repeatable>
insert transactions (control_desc, control_value)
select control_desc, control_value
from control
where control_desc = 'ABCDE'
go
select * from control
select * from transactions
--</Repeatable>
|||The following query may help you...
Code Snippet
create table control (
id int,
id_value int)
Go
create table Transactions (
trans_id int,
trans_type int,
amount float,
trans_date datetime,
uid uniqueidentifier)
Go
Insert Into control values(1,0)--Initiating the value
Go
Create Trigger Trg_Insert_Transactions
On Transactions For Insert
As
Begin
SET NOCOUNT ON;
Declare @.Id as int;Update control WITH (ROWLOCK)
Set
@.Id = Id_value = (Id_Value +1)
Where
id =1;
Update Transactions
Set
trans_id = @.Id
Where
uid = (Select Uid From Inserted)
End
Go
Insert Into Transactions values(null, 1, 10,getdate(),newid())
GO
select * from Transactions
select * from control
Insert Trigger
I am facing problem in creating a insert trigger for the following scenario.
i have transactions, control tables
whenever i insert a record in transactions it should get value from the control table, increment that value in control table and update the same value as transaction_id for new transaction in transaction table.
control table has these fields (control_desc, control_value)
can some one help me to write a trigger (insert trigger) in transactions table.
Tanks for the Help
Coudl you please provide more information like DDL code, showing which column you want to update etc.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for the replay
Fields in Transactions Table : trans_id,trans_type,amount,trans_date,uid
Fields in Control Table: id, id_value
I am inserting all the fields in transction table except trans_id when insert trigger fires i want to take id_value from control table which id is "trans_id" in id coulmn update the same in the trans_id field in transactiontable.
Can u help me reating this trigger.
Regards
|||Try this
--<Run Once>
drop table Transactions
drop table control
go
create table control
(
control_desc varchar(5) not null primary key,
control_value int not null
)
create table Transactions
(
trans_id int not null identity,
control_desc varchar(5) not null foreign key references control(control_desc),
control_value int not null
)
go
create trigger ti_Transactions on Transactions for insert
as
set nocount on
update c
set c.control_value = c.control_value + 1
from control c
join inserted i
on i.control_desc = c.control_desc
go
insert control select 'ABCDE', 10001
go
--</Run Once>
--<Repeatable>
insert transactions (control_desc, control_value)
select control_desc, control_value
from control
where control_desc = 'ABCDE'
go
select * from control
select * from transactions
--</Repeatable>
|||The following query may help you...
sql
Code Snippet
create table control (
id int,
id_value int)
Go
create table Transactions (
trans_id int,
trans_type int,
amount float,
trans_date datetime,
uid uniqueidentifier)
Go
Insert Into control values(1,0)--Initiating the value
Go
Create Trigger Trg_Insert_Transactions
On Transactions For Insert
As
Begin
SET NOCOUNT ON;
Declare @.Id as int;Update control WITH (ROWLOCK)
Set
@.Id = Id_value = (Id_Value +1)
Where
id =1;
Update Transactions
Set
trans_id = @.Id
Where
uid = (Select Uid From Inserted)
End
Go
Insert Into Transactions values(null, 1, 10,getdate(),newid())
GO
select * from Transactions
select * from control
Sunday, February 19, 2012
Insert or Update SSIS for Composite Primary Key
Hi ,
We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .
Jegan.T
Jeagant
I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.
Hopes this help.
Peter Avenant
|||Hi Peter ,
Thanks for the suggestion .but my source table does not have surrogate key.
Jegan
|||Jegan,
What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).
Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.
Rafael Salas
|||
Hi Jegan,
Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.
Here is what you need to set in each page of the wizard:
In the first page you will need to set c1,c2, etc as your "business keys"|||
Tom,
Thats Great Thanks for the suggestion
Jegan
|||Hi Peter,
The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.
Checking for both checksum and binary_checksum does not resolve the problem.
MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.
Thanks
|||Linkies,
As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca
Jakes
Insert or Update SSIS for Composite Primary Key
Hi ,
We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .
Jegan.T
Jeagant
I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.
Hopes this help.
Peter Avenant
|||Hi Peter ,
Thanks for the suggestion .but my source table does not have surrogate key.
Jegan
|||Jegan,
What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).
Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.
Rafael Salas
|||
Hi Jegan,
Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.
Here is what you need to set in each page of the wizard:
In the first page you will need to set c1,c2, etc as your "business keys"|||
Tom,
Thats Great Thanks for the suggestion
Jegan
|||Hi Peter,
The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.
Checking for both checksum and binary_checksum does not resolve the problem.
MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.
Thanks
|||Linkies,
As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca
Jakes
Insert or Update SSIS for Composite Primary Key
Hi ,
We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .
Jegan.T
Jeagant
I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.
Hopes this help.
Peter Avenant
|||Hi Peter ,
Thanks for the suggestion .but my source table does not have surrogate key.
Jegan
|||Jegan,
What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).
Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.
Rafael Salas
|||Hi Jegan,
Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.
Here is what you need to set in each page of the wizard:
In the first page you will need to set c1,c2, etc as your "business keys"|||Tom,
Thats Great Thanks for the suggestion
Jegan
|||Hi Peter,
The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.
Checking for both checksum and binary_checksum does not resolve the problem.
MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.
Thanks
|||Linkies,
As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca
Jakes
Insert or Update SSIS for Composite Primary Key
Hi ,
We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .
Jegan.T
Jeagant
I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.
Hopes this help.
Peter Avenant
|||Hi Peter ,
Thanks for the suggestion .but my source table does not have surrogate key.
Jegan
|||Jegan,
What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).
Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.
Rafael Salas
|||
Hi Jegan,
Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.
Here is what you need to set in each page of the wizard:
In the first page you will need to set c1,c2, etc as your "business keys"|||
Tom,
Thats Great Thanks for the suggestion
Jegan
|||Hi Peter,
The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.
Checking for both checksum and binary_checksum does not resolve the problem.
MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.
Thanks
|||Linkies,
As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca
Jakes
Insert or Update SSIS for Composite Primary Key
Hi ,
We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .
Jegan.T
Jeagant
I assume that your warehouse has all 5 keys as the business keys in your warehouse. You should then have a surrogate key in the warehouse which is what you would like to lookup. Create a Lookup joining all the source keys to business keys returning the surrogate key. On the error handling select ignore error. Add a conditional split looking for ISNULL(SurrogateKey). This would be your inserts. Updates would be the rest. You could further reduce the unchanged records by implementing a checksum. You can find a checksum component here www.sqlis.com.
Hopes this help.
Peter Avenant
|||Hi Peter ,
Thanks for the suggestion .but my source table does not have surrogate key.
Jegan
|||Jegan,
What about using a Lookup task against your destination table; using all 5 keys columns in the columns tab to define the 'join'. Then configure error output to redirect row. Doing this all non existing rows (no match) are send to the error output (your inserts); and all existing rows are sent to the Lookup output (your updates).
Remember that the Lookup task behavior by default is to cache the whole result set; so this would impact directly your memory resources.
Rafael Salas
|||
Hi Jegan,
Have a look at the "slowly changing dimension" data flow transformation element. It does exactly what you need.
Here is what you need to set in each page of the wizard:
In the first page you will need to set c1,c2, etc as your "business keys"|||
Tom,
Thats Great Thanks for the suggestion
Jegan
|||Hi Peter,
The Checksum function that you are suggesting is not a good idea as seldom it returns same value for different combination of column values.
Checking for both checksum and binary_checksum does not resolve the problem.
MS documentation states clearly that the value is not gauranteed to be unique though it will be in most cases.
Thanks
|||Linkies,
As dit jy is, stuur vir my jou foon nommer dat ons weer n slag kan chat. My e-mail address is jolivier@.pizzadelight.ca
Jakes
Subscribe to:
Posts (Atom)