January 25, 2010 at 10:45 am
Hi folks,
I have these two tables:
-the transactional table:
CREATE TABLE [dbo].[application_lead](
[application_lead_id] [int] IDENTITY(1,1) NOT NULL,
[date_created] [datetime] NOT NULL,
[date_modified] [datetime] NULL,
[loan_amount_requested] [smallmoney] NULL,
[application_lead_status_id] [int] NOT NULL,
CONSTRAINT [PK_application_lead] PRIMARY KEY CLUSTERED
(
[application_lead_id] ASC
)
GO
- the reference table:
CREATE TABLE [dbo].[application_lead_status](
[application_lead_status_id] [int] IDENTITY(1,1) NOT NULL,
[date_created] [datetime] NOT NULL,
[name] [varchar](80) NULL,
[active] [bit] NOT NULL,
CONSTRAINT [PK_application_lead_status] PRIMARY KEY CLUSTERED
(
[application_lead_status_id] ASC
)
GO
ALTER TABLE [dbo].[application_lead_status]
ADD CONSTRAINT [DV_application_lead_status_active] DEFAULT ((1)) FOR [active]
GO
Obviously, there's FK logic link between application_lead.application_lead_status_id AND application_lead_status.application_lead_status_id
The problem I need to solve is this:
- whenever an insert occurs on application_lead table with an application_lead_status_id value that doesn't exist in application_lead_status table, I need to somehow insert the new value in application_lead_status assigning an id in the process, retrieve that id and use it in original insert that fired the trigger. Oh yeah, I had an "instead of" trigger in mind to solve this, however there are some transactional considerations that I have to keep in mind.
If this thing could be solved via any other way ON THE DATABASE SIDE (not on application side ), example an insert stored procedure, please feel free to tell me or hint it to how to do it. I wouldn't mind an "instead of" trigger example if you have something like this handy.
Thanks a bunch,
January 25, 2010 at 11:04 am
Don't use the identity property in your reference table !
Create a trigger for Insert and and use the inserted object to load your target table.
Keep in mind to do this SET wise !
Insert into ref-table
select T.* from table T
inner join interted I
on T.key = I.key
I coded it this way because I think (cannot test right now) inserted only conatins the actually provided column values, whilest the extra join will also have the provided "default" vlues of all columns (if not mentioned in the actual insert statement)
You may want to test en use of only the inserted object of the trigger, so you avoid the extra ( and useless in that case) join
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 25, 2010 at 11:56 am
I am sorry to say but that is a very broad solution that covers only marginally what I wanted to find out.
Yes, I am thinking SET based, but there's much more to just a inner join between the inserted and the reference table.
The insert that fires the trigger (assuming that a trigger will take care of this) will occur on the transactional table, not the reference table. The firing insert should like this:
INSERT INTO [application_lead]
(
[date_created],
[date_modified],
[loan_amount_requested],
[name]
)
VALUES (...)
Notice that the last column in the insert is a value intended in fact for the reference table, the mismatch between the insert and the underlying table being handled at the trigger level. Once the new value [name] is properly deposited in the reference table, the corresponding id is retrieved and used the initial INSERT.
Is this the way to do it?
Also, if I have to think it SET wise, that means inside the trigger I'll have to create a small temporary table that will loop through all the new [name] values that don't exist yet in the reference table. Why do I need the loop? Because for every single new [name] reference value, I need to assign the id, collect the id and use in the firing insert... I'm not even sure how I'm supposed to tackle this part, transactional wise. I don't want to have orphaned reference ids that are not used for anything because of some racing condition.
Having a temporary table created in the trigger is bad idea ( or so I've been told ).
Please advise.
January 26, 2010 at 12:42 am
Well .... altough I cannot get the point of using a "don't know what status it is in, but it is not a known status" ....
This may be what you asked for ...
ALTER TABLE dbo.[application_lead_status]
ADD CONSTRAINTDF_application_lead_status_date_created DEFAULT getdate() FOR date_created ;
ALTER TABLE dbo.[application_lead_status]
ADD CONSTRAINT DF_application_lead_status_active DEFAULT 0 FOR [active] ;
go
alter table [dbo].[application_lead_status]
add [CreatedBy_application_lead_id] [int] ;
go
alter table [dbo].[application_lead]
alter column [application_lead_status_id] [int] NULL ;
go
alter trigger tr_application_lead
on [application_lead]
for insert
as
begin
declare @wrk table ( [CreatedBy_application_lead_id] [int] not null
, [application_lead_status_id] [int] not null)
Insert into [application_lead_status] ( [CreatedBy_application_lead_id] )
output inserted.[CreatedBy_application_lead_id], inserted.[application_lead_status_id]
into @wrk
Select I.[application_lead_id]
from inserted I
where I.[application_lead_status_id] is null ;
update T
set [application_lead_status_id] = w.[application_lead_status_id]
from [application_lead] T
inner join @wrk w
on w.[CreatedBy_application_lead_id] = T.[application_lead_id] ;
end ;
go
Insert into [application_lead] ([date_created] , [date_modified], [loan_amount_requested], [application_lead_status_id])
values ( getdate(), getdate(), 1 ,null)
go
Select *
from [application_lead]
Select *
from [application_lead_status]
go
Keep in mind it is up to you ( euhm ... your business data responsibles ) to figure out what kind of status was meanth when the row was inserted and how take care of symantic duplicates, ...
It is not because you can do something, you should do it !
Provide / Define your lookup parameters before they can be used at all !!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 28, 2010 at 12:39 pm
any progress ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply