Trigger-Procedure Transact SQL

[andyscamilla]

Hola Buenas noches. soy nuevo en transact SQl y tengo algunas dudas

bueno le adjunto lo que he hecho

/*Curso (codigo_c, nombre, profesor), cuya clave primaria es código_c
Alumno (rut, nombre, sexo, edad), cuya clave primaria es rut
Nota (codigo_c, rut, semestre, situación)*/

CREATE TABLE Curso(
codigo_c INTEGER NOT NULL,
nombre VARCHAR(255),
profesor VARCHAR(255),
PRIMARY KEY (codigo_c)
);

CREATE TABLE Alumno(
rut VARCHAR(12) NOT NULL,
nombre VARCHAR(255),
sexo VARCHAR(9),
edad INTEGER,
PRIMARY KEY (rut),
CHECK (sexo IN ('femenino', 'masculino'))
);

CREATE TABLE Nota(
codigo_c INTEGER NOT NULL,
rut VARCHAR(12) NOT NULL,
semestre VARCHAR(10),
situacion VARCHAR(20),
PRIMARY KEY (codigo_c,rut),
FOREIGN KEY (codigo_c) REFERENCES Curso(codigo_c),
FOREIGN KEY (rut) REFERENCES Alumno(rut),
CHECK (semestre IN ('I','II')),
CHECK (situacion IN ('Aprobado','No-Aprobado'))
);

/* INSERT */

insert into Curso values (1, 'TBB', 'prof_1');
insert into Curso values (2, 'TBB2', 'prof_2');
insert into Curso values (3, 'Algebra', 'prof_3');
insert into Curso values (4, 'Calculo', 'prof_3');
insert into Curso values (5, 'Ing SW', 'prof_4');
insert into Curso values (6, 'SFC', 'prof_5');


insert into Alumno values ('11111111-1','alumno_1','masculino',20);
insert into Alumno values ('22222222-2','alumno_2','femenino',23);
insert into Alumno values ('33333333-3','alumno_3','masculino',25);
insert into Alumno values ('44444444-4','alumno_4','femenino',27);
insert into Alumno values ('66666666-5','alumno_5','masculino',21);


insert into Nota values (1,'11111111-1','I','Aprobado');
insert into Nota values (2,'33333333-3','II','Aprobado');
insert into Nota values (3,'22222222-2','I','Aprobado');
insert into Nota values (4,'44444444-4','II','Aprobado');
insert into Nota values (5,'11111111-1','I','Aprobado');
insert into Nota values (2,'22222222-2','II','No-Aprobado');
insert into Nota values (4,'33333333-3','I','Aprobado');
insert into Nota values (5,'44444444-4','I','No-Aprobado');
insert into Nota values (6,'44444444-4','II','Aprobado');


--1)1. Crear un trigger que no permita modificar o eliminar un registro de la tabla nota

CREATE TRIGGER TRIGGER_no_permite_update_delete
ON Nota
INSTEAD OF DELETE,UPDATE
AS
BEGIN
PRINT 'No se permiten cambios ni eliminaciones sobre la tabla NOTA';
END


SELECT * FROM NOTA



UPDATE Nota set codigo_c = 6 where codigo_c=1;

insert into Nota values (6,'66666666-5','II','No-Aprovado');

delete
from nota
where codigo_c=6


/* 2. Generar un procedimiento almacenado que muestre el nombre del profesor,
la cantidad de cursos que dicta y el número de alumnos aprobados en sus cursos */


CREATE PROCEDURE SP_Ficha_Docente
@nombre_profesor VARCHAR(255)OUTPUT,
@cantidad_curso_dicta INTEGER OUTPUT,
@cantidad_alumnos_aprobador INTEGER OUTPUT
AS
BEGIN

select @nombre_profesor = CUR.profesor,
@cantidad_curso_dicta = count (Cur.codigo_c),
@cantidad_alumnos_aprobador = Count (N.situacion)
from Curso CUR,Nota N,Alumno AL
where CUR.codigo_c = N.codigo_c and AL.rut = N.rut and N.situacion='Aprobado'
GROUP BY (cur.profesor)

END

-- aL EJECUTAR CONSULTA SOLO ME DEVUELVE 1 SOLO VALOR, (EL ULTIMO)

DECLARE
@nombre_profesor VARCHAR(255),
@cantidad_curso_dicta INTEGER,
@cantidad_alumnos_aprobador INTEGER
EXECUTE SP_Ficha_Docente
@nombre_profesor OUTPUT,
@cantidad_curso_dicta OUTPUT,
@cantidad_alumnos_aprobador OUTPUT
PRINT @nombre_profesor +' '+CONVERT (VARCHAR(10),@cantidad_curso_dicta)
+' '+CONVERT(VARCHAR(10),@cantidad_alumnos_aprobador)


/*
PROFESOR CANTIDAD_CURSOS CANTIDAD_APROBADOS
prof_5 1 1
*/

-- CONSULTA SIN TRANSACT====================================

select CUR.profesor,count (Cur.codigo_c)AS CANTIDAD_CURSOS,Count (N.situacion)AS CANTIDAD_APROBADOS
from Curso CUR,Nota N,Alumno AL
where CUR.codigo_c = N.codigo_c and AL.rut = N.rut and N.situacion='Aprobado'
GROUP BY (cur.profesor)

/*

PROFESOR CANTIDAD_CURSOS CANTIDAD_APROBADOS
prof_1 1 1
prof_2 1 1
prof_3 3 3
prof_4 1 1
prof_5 1 1

*/

--================================================== ============




/* 3. Crear un trigger que no permita que un alumno pueda inscribir
más de 5 asignaturas en el mismo semestre .*/


CREATE TRIGGER TRIGGER_No_mas_5_asignaturas
on curso
INSTEAD OF INSERT
AS
BEGIN
if
SELECT distinct count(CUR.nombre)
from curso CUR,nota N, Alumno AL
where CUR.codigo_c = N.codigo_c and
AL.rut = N.rut and
N.semestre = 'I'
print 'no se puede inscribir mas ramos'
else
print 'aun se puede inscribir ramos'
END




principalmente en el ejercicio dos la consulta segun yo esta bien hecha pero no se como retornar todos los valores pues solo me devuelve la ultima linea

y en ejercicio 3 si me pueden dar un ejemplo ideal puesto que no se como abordar el problema.
Muchas gracias

[Libras]

si quieres retornar todos los valores del query quitale la asignacion a las variables ya que asi solo te retorna un valor, si quieres manejar un "array" mejor mete estos valores en una tabla temporal

select datos into #temp from tabla

Saludos!!

[andyscamilla]

y con respecto al ultimo trigger (eje 3) saben como realizarlo, pude hacer bien la consulta pero no se como realizarlo

SELECT count (distinct (N.codigo_c)) as cantidad_ramos
from curso CUR,nota N, Alumno AL
where CUR.codigo_c = N.codigo_c and
N.rut = '44444444-4' and
N.semestre = 'II'




donde '44444444-4' deberia ser una variable que contenga el rut del usuario
y 'II' deberia ser otra variable con el semestre requerido.

[Libras]

cuando haces un insert o un delete un trigger puede consultar 2 tablas una que se llama inserted y otra deleted en donde puedes ver cual es el usuario que esta insertando por ejemplo

Código SQL:

CREATE TRIGGER ON TABLE after INSERT
AS
DECLARE @variable AS INT
SELECT id FROM inserted
 
SELECT @variable=COUNT(*) FROM TABLE WHERE usuario=id
IF @variable=4 THEN
  print 'no puedes agregar mas'
ELSE
  print 'otro mensaje'
 

Saludos!!!