wN1PvL3e

1CREATE TABLE [dbo].[Arriendo_Autos] (
2    [Patente] nchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
3    [Nombre_Auto] nvarchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
4    [cTipo] smallint NOT NULL,
5    [cMarca] smallint NOT NULL,
6    [Año] datetime NULL,
7    [Modelo_Auto] varchar(16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
8    [Asientos] smallint NULL,
9    [Combustible] varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
10    [Tarifa_Diaria] money NOT NULL,
11    PRIMARY KEY CLUSTERED ([Patente]),
12    CONSTRAINT [Arriendo_Autos_Marca_fk] FOREIGN KEY ([cMarca]) 
13    REFERENCES [dbo].[Arriendo_Marcas] ([cMarca]) 
14    ON UPDATE NO ACTION
15    ON DELETE NO ACTION,
16    CONSTRAINT [Arriendo_Autos_Tipo_fk] FOREIGN KEY ([cTipo]) 
17    REFERENCES [dbo].[Arriendo_TipoAuto] ([cTipo]) 
18    ON UPDATE NO ACTION
19    ON DELETE NO ACTION
20)
21ON [PRIMARY]
22GO
23
24
25
26CREATE TABLE [dbo].[Arriendo_TipoAuto] (
27    [cTipo] smallint IDENTITY(1, 1) NOT NULL,
28    [Tipo_Auto] varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
29    CONSTRAINT [Arriendo_TipoAuto_pk] PRIMARY KEY CLUSTERED ([cTipo])
30)
31ON [PRIMARY]
32GO
33
34CREATE TABLE [dbo].[Arriendo_Solicitud] (
35    [cSolicitud] int IDENTITY(1, 1) NOT NULL,
36    [Patente] nchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
37    [fInicio] datetime NOT NULL,
38    [fTermino] datetime NOT NULL,
39    [Rut] varchar(11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
40    [cFPago] smallint NOT NULL,
41    PRIMARY KEY CLUSTERED ([cSolicitud]),
42    CONSTRAINT [Arriendo_Solicitud_Autos_fk] FOREIGN KEY ([Patente]) 
43    REFERENCES [dbo].[Arriendo_Autos] ([Patente]) 
44    ON UPDATE NO ACTION
45    ON DELETE NO ACTION,
46    CONSTRAINT [Arriendo_Solicitud_FPago_fk] FOREIGN KEY ([cFPago]) 
47    REFERENCES [dbo].[Arriendo_FormaPago] ([cFPago]) 
48    ON UPDATE NO ACTION
49    ON DELETE NO ACTION,
50    CONSTRAINT [Arriendo_Solicitud_Rut_fk] FOREIGN KEY ([Rut]) 
51    REFERENCES [dbo].[Arriendo_Clientes] ([Rut]) 
52    ON UPDATE NO ACTION
53    ON DELETE NO ACTION
54)
55ON [PRIMARY]
56GO
57	
58CREATE PROCEDURE SP_Arriendo_TipoAuto_DU @cTipo SMALLINT 
59AS 
60
61DECLARE @salida nvarchar(30)
62
63BEGIN
64    If NOT EXISTS (
65        SELECT Patente
66        FROM dbo.Arriendo_Autos WHERE cTipo=@cTipo
67    )
68
69    If NOT EXISTS( 
70        SELECT Patente
71        FROM dbo.Arriendo_Solicitud WHERE cTipo=@cTipo
72    )
73END
74
75BEGIN
76    DELETE T
77    FROM Arriendo_TipoAuto T 
78    INNER JOIN Arriendo_Autos A
79        ON T.cTipo=A.cMarca
80    INNER JOIN Arriendo_Solicitud S
81        ON A.Patente= S.Patente
82    WHERE A.cTipo=@cTipo
83
84    SET @salida='Registros Eliminados' 
85END