CODEWITHSUNDEEP
sql-servert-sql
delete
delete

Reputation:

How can I create this trigger for Microsoft SQL Server?

I have this limitation I have to implement server-side (in the database). Normally I'd do this on the client side, but I'd figure why not learn the server aspect. :p

I don't want my Clients to be able to rent a Movie from the XXX genre if they are under 18.

Here is the script I used to generate the tables:

-- =============================================
-- Sergio's Lab Tests MWA HA HA
-- =============================================

use AlquilerPeliculas

create table Client
(
    ID int primary key not null identity(1,1),
    Address nvarchar(1024) not null,
    Phone nvarchar(256) not null,
    NIT nvarchar(32) not null
)
go
create table Genre
(
    ID int primary key not null identity(1,1),
    Name nvarchar(256)
)
go
create table Movie
(
    ID int primary key not null identity(1,1),
    Name nvarchar(256) not null,
    IDGenre int foreign key references Genre(ID)
)
go    
create table Natural
(
    IDCliente int primary key references Cliente(ID),
    Age as datediff(d, FechaDeNacimiento,getdate())/365.00,


    Nombre nvarchar(1024) not null,
    ApellidoPaterno nvarchar(512) not null,
    FechaDeNacimiento datetime,
    Sexo varchar(1) not null check(Sexo='M' or Sexo='F')
)
go
create table Alquiler
(
    ID int primary key not null identity(1,1),
    FechaDeAlquiler datetime,
    Total nvarchar(20) not null,
    IDClient int foreign key references Client(ID)
)
go
create table Ejemplar
(
    ID int primary key not null identity(1,1),
    NumeroDeEjemplar nvarchar(256) not null,
    Descripcion nvarchar(1024),
    IDFormato int foreign key references Formato(ID),
    IDPelicula int foreign key references Pelicula(ID)
)
go
create table DetalleAlquiler
(
    ID int primary key not null identity(1,1),
    IDEjemplar int foreign key references Ejemplar(ID),
    IDAlquiler int foreign key references Alquiler(ID),
    PrecioDeAlquiler nvarchar(128),
    FechaDevolucion datetime,
    FechaDevolucionProgramada datetime
)

I asked a friend what I should use and he said a Trigger, but it's my understanding that a trigger is a function that's run when the triggers conditions are met, right? If I used a trigger I'd have to insert then delete a naughty record right?

Thanks for the help.

Upvotes: 0

Views: 230

Answers (1)

Sam Saffron
Sam Saffron

Reputation: 131112

Read all about triggers here

They come in many flavors, they can run before every insert / after every update and so on. The triggering of the trigger is a blanket action, if you have a BEFORE trigger, it will always run before.

Then, if you want to do any filtering or error handling, for example, only run a bit of code if a particular column has a particular value, you include that inside the trigger (in conditional code).

Generally, I recommend against using triggers, they can often make locking more complicated and are "hidden" in a place no one tends to look (which make them very hard to debug).

Another approach you can take DB-wise, it have particular stored procs that you call, instead of calling tables directly, eg: spRentMovie.

Upvotes: 1

Related Questions