Default values

I have a table call Master and a table called Rental. They both have a
status field that are named differently. What i want to happen is for the
Status in the Rental table to default to whatever the status is in the
Master table whenever it is imported to the Rental table. ANyone have any
suggestions for this. I dont even know where to start. Thanks>whenever it is imported to the Rental table
What do you mean by imported? What process is running?
One possible answer to your question, if my guess work happens to on
target:
INSERT Rental (A, B, C)
SELECT A, B, X
FROM Master
Roy
On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
<ben.watts@.aaronnickellhomes.com> wrote:
>I have a table call Master and a table called Rental. They both have a
>status field that are named differently. What i want to happen is for the
>Status in the Rental table to default to whatever the status is in the
>Master table whenever it is imported to the Rental table. ANyone have any
>suggestions for this. I dont even know where to start. Thanks
>|||It already holds the specific jobnumber in master but whenver a user enters
that specific job into the rental table via infopath I want it to
automatically take on the status from the master table. But allow it to be
changed in the rental table later. So basically just the first default and
then after that i want to be able to change it manually and it leave it like
that
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
> >whenever it is imported to the Rental table
> What do you mean by imported? What process is running?
> One possible answer to your question, if my guess work happens to on
> target:
> INSERT Rental (A, B, C)
> SELECT A, B, X
> FROM Master
> Roy
> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have any
>>suggestions for this. I dont even know where to start. Thanks|||I pretty much need taken thru this all the way, like where do I insert that
code?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
> >whenever it is imported to the Rental table
> What do you mean by imported? What process is running?
> One possible answer to your question, if my guess work happens to on
> target:
> INSERT Rental (A, B, C)
> SELECT A, B, X
> FROM Master
> Roy
> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have any
>>suggestions for this. I dont even know where to start. Thanks|||Two approaches. One is to write a subquery into the INSERT into the
Rental table. It might vaguely resemble something like:
INSERT Rental (jobnumber, B, RentalStatus)
SELECT 12345, B,
(select MasterStatus from Master
where master.jobnumber = '12345')
The other approach is an INSERT trigger on the Rental table, which
would include something like:
UPDATE Rental
SET RentalStatus = (select MasterStatus from Master
where master.jobnumber = Rental.jobnumber)
WHERE Rental.jobnumber IN
(select jobnumber from INSERTED)
Roy Harvey
Beacon Falls, CT
On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
<ben.watts@.aaronnickellhomes.com> wrote:
>It already holds the specific jobnumber in master but whenver a user enters
>that specific job into the rental table via infopath I want it to
>automatically take on the status from the master table. But allow it to be
>changed in the rental table later. So basically just the first default and
>then after that i want to be able to change it manually and it leave it like
>that
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have any
>>suggestions for this. I dont even know where to start. Thanks
>|||I f I did a trigger would it allow me to change the value whenever I wanted
and allow me to keep the change?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.4ax.com...
> Two approaches. One is to write a subquery into the INSERT into the
> Rental table. It might vaguely resemble something like:
> INSERT Rental (jobnumber, B, RentalStatus)
> SELECT 12345, B,
> (select MasterStatus from Master
> where master.jobnumber = '12345')
> The other approach is an INSERT trigger on the Rental table, which
> would include something like:
> UPDATE Rental
> SET RentalStatus => (select MasterStatus from Master
> where master.jobnumber = Rental.jobnumber)
> WHERE Rental.jobnumber IN
> (select jobnumber from INSERTED)
> Roy Harvey
> Beacon Falls, CT
> On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
> <ben.watts@.aaronnickellhomes.com> wrote:
>>It already holds the specific jobnumber in master but whenver a user
>>enters
>>that specific job into the rental table via infopath I want it to
>>automatically take on the status from the master table. But allow it to
>>be
>>changed in the rental table later. So basically just the first default
>>and
>>then after that i want to be able to change it manually and it leave it
>>like
>>that
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for
>>the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have
>>any
>>suggestions for this. I dont even know where to start. Thanks
>>|||What is wront with this trigger?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Rental_Statustrig]
ON [dbo].[Rental_Info]
Update Rental_Info
Set Rental_Status =
(select job_status
from Master
where Master.wwpjobnumber = Rental_Status.wwpjobnumber)
where Rental_Info.wwpjobnumber IN
(select wwpjobnumber
from Master)
END
GO
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:uRgS3n9tGHA.3964@.TK2MSFTNGP04.phx.gbl...
>I f I did a trigger would it allow me to change the value whenever I wanted
>and allow me to keep the change?
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.4ax.com...
>> Two approaches. One is to write a subquery into the INSERT into the
>> Rental table. It might vaguely resemble something like:
>> INSERT Rental (jobnumber, B, RentalStatus)
>> SELECT 12345, B,
>> (select MasterStatus from Master
>> where master.jobnumber = '12345')
>> The other approach is an INSERT trigger on the Rental table, which
>> would include something like:
>> UPDATE Rental
>> SET RentalStatus =>> (select MasterStatus from Master
>> where master.jobnumber = Rental.jobnumber)
>> WHERE Rental.jobnumber IN
>> (select jobnumber from INSERTED)
>> Roy Harvey
>> Beacon Falls, CT
>> On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>It already holds the specific jobnumber in master but whenver a user
>>enters
>>that specific job into the rental table via infopath I want it to
>>automatically take on the status from the master table. But allow it to
>>be
>>changed in the rental table later. So basically just the first default
>>and
>>then after that i want to be able to change it manually and it leave it
>>like
>>that
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for
>>the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have
>>any
>>suggestions for this. I dont even know where to start. Thanks
>>
>|||If it is an INSERT trigger it will only execute when rows are
inserted. It would have to be an UPDATE trigger to mess up your
changes.
Roy
On Fri, 4 Aug 2006 10:24:12 -0500, "Ben Watts"
<ben.watts@.aaronnickellhomes.com> wrote:
>I f I did a trigger would it allow me to change the value whenever I wanted
>and allow me to keep the change?
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.4ax.com...
>> Two approaches. One is to write a subquery into the INSERT into the
>> Rental table. It might vaguely resemble something like:
>> INSERT Rental (jobnumber, B, RentalStatus)
>> SELECT 12345, B,
>> (select MasterStatus from Master
>> where master.jobnumber = '12345')
>> The other approach is an INSERT trigger on the Rental table, which
>> would include something like:
>> UPDATE Rental
>> SET RentalStatus =>> (select MasterStatus from Master
>> where master.jobnumber = Rental.jobnumber)
>> WHERE Rental.jobnumber IN
>> (select jobnumber from INSERTED)
>> Roy Harvey
>> Beacon Falls, CT
>> On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>It already holds the specific jobnumber in master but whenver a user
>>enters
>>that specific job into the rental table via infopath I want it to
>>automatically take on the status from the master table. But allow it to
>>be
>>changed in the rental table later. So basically just the first default
>>and
>>then after that i want to be able to change it manually and it leave it
>>like
>>that
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for
>>the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have
>>any
>>suggestions for this. I dont even know where to start. Thanks
>>
>|||Please do not make duplicate posts. You only waited one minute for a reply
before you decided to start a new thread with the same question.
The word END should not be included.
--
HTH
Kalen Delaney, SQL Server MVP
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:etGT709tGHA.2172@.TK2MSFTNGP05.phx.gbl...
> What is wront with this trigger?
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TRIGGER [Rental_Statustrig]
> ON [dbo].[Rental_Info]
>
> Update Rental_Info
> Set Rental_Status => (select job_status
> from Master
> where Master.wwpjobnumber = Rental_Status.wwpjobnumber)
> where Rental_Info.wwpjobnumber IN
> (select wwpjobnumber
> from Master)
> END
> GO
> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
> news:uRgS3n9tGHA.3964@.TK2MSFTNGP04.phx.gbl...
>>I f I did a trigger would it allow me to change the value whenever I
>>wanted and allow me to keep the change?
>> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.4ax.com...
>> Two approaches. One is to write a subquery into the INSERT into the
>> Rental table. It might vaguely resemble something like:
>> INSERT Rental (jobnumber, B, RentalStatus)
>> SELECT 12345, B,
>> (select MasterStatus from Master
>> where master.jobnumber = '12345')
>> The other approach is an INSERT trigger on the Rental table, which
>> would include something like:
>> UPDATE Rental
>> SET RentalStatus =>> (select MasterStatus from Master
>> where master.jobnumber = Rental.jobnumber)
>> WHERE Rental.jobnumber IN
>> (select jobnumber from INSERTED)
>> Roy Harvey
>> Beacon Falls, CT
>> On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>It already holds the specific jobnumber in master but whenver a user
>>enters
>>that specific job into the rental table via infopath I want it to
>>automatically take on the status from the master table. But allow it to
>>be
>>changed in the rental table later. So basically just the first default
>>and
>>then after that i want to be able to change it manually and it leave it
>>like
>>that
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for
>>the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have
>>any
>>suggestions for this. I dont even know where to start. Thanks
>>
>>
>|||I give up. Which thread are we working with here?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ORxlP89tGHA.4852@.TK2MSFTNGP02.phx.gbl...
> Please do not make duplicate posts. You only waited one minute for a reply
> before you decided to start a new thread with the same question.
> The word END should not be included.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
> news:etGT709tGHA.2172@.TK2MSFTNGP05.phx.gbl...
>> What is wront with this trigger?
>> SET ANSI_NULLS ON
>> GO
>> SET QUOTED_IDENTIFIER ON
>> GO
>> CREATE TRIGGER [Rental_Statustrig]
>> ON [dbo].[Rental_Info]
>>
>> Update Rental_Info
>> Set Rental_Status =>> (select job_status
>> from Master
>> where Master.wwpjobnumber = Rental_Status.wwpjobnumber)
>> where Rental_Info.wwpjobnumber IN
>> (select wwpjobnumber
>> from Master)
>> END
>> GO
>> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
>> news:uRgS3n9tGHA.3964@.TK2MSFTNGP04.phx.gbl...
>>I f I did a trigger would it allow me to change the value whenever I
>>wanted and allow me to keep the change?
>> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.4ax.com...
>> Two approaches. One is to write a subquery into the INSERT into the
>> Rental table. It might vaguely resemble something like:
>> INSERT Rental (jobnumber, B, RentalStatus)
>> SELECT 12345, B,
>> (select MasterStatus from Master
>> where master.jobnumber = '12345')
>> The other approach is an INSERT trigger on the Rental table, which
>> would include something like:
>> UPDATE Rental
>> SET RentalStatus =>> (select MasterStatus from Master
>> where master.jobnumber = Rental.jobnumber)
>> WHERE Rental.jobnumber IN
>> (select jobnumber from INSERTED)
>> Roy Harvey
>> Beacon Falls, CT
>> On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>It already holds the specific jobnumber in master but whenver a user
>>enters
>>that specific job into the rental table via infopath I want it to
>>automatically take on the status from the master table. But allow it
>>to be
>>changed in the rental table later. So basically just the first default
>>and
>>then after that i want to be able to change it manually and it leave it
>>like
>>that
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>>I have a table call Master and a table called Rental. They both have
>>>a
>>>status field that are named differently. What i want to happen is
>>>for the
>>>Status in the Rental table to default to whatever the status is in
>>>the
>>>Master table whenever it is imported to the Rental table. ANyone
>>>have any
>>>suggestions for this. I dont even know where to start. Thanks
>>>
>>
>>
>|||Thanks you all, I got it working using everyone of your expertise. Many
thanks.
Ben
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:74r6d251koh0rgerp3m6tt64rcm084mf2e@.4ax.com...
> If it is an INSERT trigger it will only execute when rows are
> inserted. It would have to be an UPDATE trigger to mess up your
> changes.
> Roy
> On Fri, 4 Aug 2006 10:24:12 -0500, "Ben Watts"
> <ben.watts@.aaronnickellhomes.com> wrote:
>>I f I did a trigger would it allow me to change the value whenever I
>>wanted
>>and allow me to keep the change?
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.4ax.com...
>> Two approaches. One is to write a subquery into the INSERT into the
>> Rental table. It might vaguely resemble something like:
>> INSERT Rental (jobnumber, B, RentalStatus)
>> SELECT 12345, B,
>> (select MasterStatus from Master
>> where master.jobnumber = '12345')
>> The other approach is an INSERT trigger on the Rental table, which
>> would include something like:
>> UPDATE Rental
>> SET RentalStatus =>> (select MasterStatus from Master
>> where master.jobnumber = Rental.jobnumber)
>> WHERE Rental.jobnumber IN
>> (select jobnumber from INSERTED)
>> Roy Harvey
>> Beacon Falls, CT
>> On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>It already holds the specific jobnumber in master but whenver a user
>>enters
>>that specific job into the rental table via infopath I want it to
>>automatically take on the status from the master table. But allow it to
>>be
>>changed in the rental table later. So basically just the first default
>>and
>>then after that i want to be able to change it manually and it leave it
>>like
>>that
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for
>>the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have
>>any
>>suggestions for this. I dont even know where to start. Thanks
>>