It's possible to make this in sql?

Question

I'm make a orders system food

I want to do this,When all the statuses of the dishes are "COMPLETE", the order automatically changes to complete

I have 4 tables

1 ORDER

ID_ORDER: OR00001

STATUS: IN PROCESS/CHANGE AUTOMATICALLY "COMPLETE"

CREATION_DATE: 17/07/2017

3 DISH

1 DISH PASTA

ID: PA00001

NAME: PASTA MIDELA

CHEF: JEFF

ID_ORDER: OR00001

STATUS: IN PROCESS

2 DISH SALAD

ID: SA00001

NAME: DIET SALAD

CHEF: ASTON

ID_ORDER: OR00001

STATUS: IN PROCESS

3 DISH MEAT

ID: ME00001

NAME: MEAT BBF

CHEF: JEFF

ID_ORDER: OR00001

STATUS: IN PROCESS

How is it possible? Thanks

CREATE TABLE [dbo].[ORDER](
    [ID_OR] [int] IDENTITY(1,1) NOT NULL,
    [ID_ORDER]  AS ('OR'+right('00000'+CONVERT([varchar],[ID_OR],(0)),(5))),
    [STATUS] [datetime] NOT NULL,
    [CREATION_DATE] [datetime] NOT NULL,
 CONSTRAINT [PK_RR] PRIMARY KEY CLUSTERED 
(
    [ID_ORDER] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

CREATE TABLE [dbo].[1_DISH](
    [ID_D1] [int] IDENTITY(1,1) NOT NULL,
    [ID_PASTA]  AS ('PA'+right('00000'+CONVERT([varchar],[ID_D1],(0)),(5))),
    [NAME] [varchar](20),
    [CHEF] [varchar](20),
    [STATUS] [datetime] NOT NULL,
    [ID_ORDER] [varchar](20)
 CONSTRAINT [PK_RR] PRIMARY KEY CLUSTERED 
(
    [ID_PASTA] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

CREATE TABLE [dbo].[2_DISH](
    [ID_D2] [int] IDENTITY(1,1) NOT NULL,
    [ID_SALAD]  AS ('SA'+right('00000'+CONVERT([varchar],[ID_D2],(0)),(5))),
    [NAME] [varchar](20),
    [CHEF] [varchar](20),
    [STATUS] [datetime] NOT NULL,
    [ID_ORDER] [varchar](20)
 CONSTRAINT [PK_RR] PRIMARY KEY CLUSTERED 
(
    [ID_SALAD] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

CREATE TABLE [dbo].[3_DISH](
    [ID_D3] [int] IDENTITY(1,1) NOT NULL,
    [ID_MEAT]  AS ('ME'+right('00000'+CONVERT([varchar],[ID_D3],(0)),(5))),
    [NAME] [varchar](20),
    [CHEF] [varchar](20),
    [STATUS] [datetime] NOT NULL,
    [ID_ORDER] [varchar](20)
 CONSTRAINT [PK_RR] PRIMARY KEY CLUSTERED 
(
    [ID_MEAT] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

Answer 1

Here's some sample code that might help you.

Two tables are created: one for the Orders and the second for the Dishes that comprise an order.

I added a DishType column to the Dish table. I changed the Status to be a varchar datatype.

declare @ORDERTABLE table 
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [STATUS] [varchar](20) NOT NULL,
    [CREATION_DATE] [datetime] NOT NULL
)

declare @DISHTABLE table
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DISHTYPE] varchar(20),
    [NAME] [varchar](20),
    [CHEF] [varchar](20),
    [STATUS] [varchar](20) NOT NULL,
    [ID_ORDER] int
)

Some sample data is inserted.

insert @ORDERTABLE (STATUS, CREATION_DATE) values
('IN PROCESS', getdate()),
('IN PROCESS', getdate());

insert @DISHTABLE (DISHTYPE, NAME, CHEF, STATUS, ID_ORDER) values
('PASTA','PASTA MIDELA','JEFF','IN PROCESS',1),
('SALAD','DIET SALAD','KIA','IN PROCESS',1),
('MEAT','MEAT BBF','BART','IN PROCESS',1),
('SALAD','CAESAR SALAD','KIA','IN PROCESS',2),
('MEAT','ROAST CHICKEN','BART','IN PROCESS',2);

update @DISHTABLE set STATUS = 'COMPLETE' where ID = 1;
update @DISHTABLE set STATUS = 'COMPLETE' where ID = 2;
update @DISHTABLE set STATUS = 'COMPLETE' where ID = 3;

select * from @DISHTABLE;

A simple way to automatically change the Order status: whenever you change the status of a dish, check how many dishes are in the order versus how many of the dishes are completed. If all dishes are complete then change the order status to complete as well.

declare @ORDERID int = 1;

update @ORDERTABLE 
    set STATUS = 'COMPLETE' 
where 
    ID = @ORDERID 
    and (select count(*) from @DISHTABLE where ID_ORDER = @ORDERID) = (select count(*) from @DISHTABLE where ID_ORDER = @ORDERID and STATUS = 'COMPLETE');


select 
    o.ID, 
    DISHES = (select count(*) from @DISHTABLE where ID_ORDER = o.ID),
    COMPLETED = (select count(*) from @DISHTABLE where ID_ORDER = o.ID and STATUS = 'COMPLETE'),
    o.STATUS
from @ORDERTABLE o;