How to create a specific statement/logic

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

How to create a specific statement/logic

Author

Topic

tommy222
Starting Member

2 Posts

Posted - 2009-02-24 : 07:35:58

Hi,

I have two main DB tables that look like this;

Table: Requests

Req_ID Surname Forename Age Manager_Name Graduate
1 Smith John 25 Tom Jones Yes
2 Example Andrew 31 Angie Holmes No

Table: Checklist Actions

Chk_Action Initial_Status Status
Send Offer No Active
Employee Talk No Active
Welcome Letter No Active

I would like to know how to create the following statement/logic….

WHEN a new record is added to Table: Requests.

THEN use this data to CREATE table entry into Table: Request Progress containing Req_ID, Forename, Surname AND each of the Chk_Actions from Table: Checklist Actions with a value of No for each action….

So the table would look something like this…..

Table: Request Progress

Request Surname Forename Send_Offer Employee_Talk Welcome_Letter
1 Smith John No No No
2 Example Andrew No No No

Could anyone tell me how to do this?

Many Thanks
Tom

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 08:51:26

Have you tried with Trigger?

tommy222
Starting Member

2 Posts

Posted - 2009-02-24 : 08:56:53

Thanks for the reply an no i haven't tried this could you tell me how?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-24 : 09:14:11

[code]
try this by sample procedure
create proc usp_sampproc
(
@id int
)
AS
SET NOCOUNT ON
BEGIN

insert into RequestProgress (Request, Surname, Forename, Send_Offer, Employee_Talk, Welcome_Letter)
select Request ,Surname, Forename,
(select Initial_Status from cChecklistActions where Chk_Action = 'Send_Offer') ,
(select Initial_Status from cChecklistActions where Chk_Action = 'Employee_Talk'),
(select Initial_Status from cChecklistActions where Chk_Action = 'Welcome_Letter')
FROM
Requests
WHERE
req_id = @id
END
SET NOCOUNT OFF
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources