Lester wrote:
> Should I be using a trigger at the database level, or try to 'translate'
> this VBA/SQL to work with SQL Server?
The former will be bulletproof against any front end whatsoever.
> sqlstring = "SELECT Events.HallsID, Events.StartTime, Events.EndTime,
> Events.StatusID, Events.EventName, Events.Date, Halls.HallsID,
> Halls.[Hall Name] FROM Halls INNER JOIN Events ON Halls.HallsID =
> Events.HallsID WHERE ((((Events.StatusID)=2 Or
> (Events.StatusID)=3))AND((((Events.StartTime) Between #12/30/1899 ' &
> StartTime & '# And #12/30/1899 ' & EndTime & '#) OR ((Events.EndTime)
> Between #12/30/1899 ' & StartTime & '# And #12/30/1899 ' & EndTime &
> '#)) AND ((Events.Date)= #' & BookDate & '# ) AND (Not(Events.EventName=
> '" & EventName & "' ))AND((Halls.HallsID)= ' & Hall & ' )));"
This definitely won't work without some fixing up, but should at least
be in the ballpark:
create trigger Events_IU on Events as
begin
declare @[EMAIL PROTECTED]
varchar(30)
declare @[EMAIL PROTECTED]
varchar(30)
declare @[EMAIL PROTECTED]
datetime
declare @[EMAIL PROTECTED]
datetime
declare @[EMAIL PROTECTED]
datetime
SELECT @[EMAIL PROTECTED]
= e.EventName,
@[EMAIL PROTECTED]
= h.[Hall Name],
@[EMAIL PROTECTED]
= e.Date,
@[EMAIL PROTECTED]
= e.StartTime,
@[EMAIL PROTECTED]
= e.EndTime
FROM Events e
INNER JOIN Halls h ON h.HallsID = e.HallsID
WHERE e.Date = inserted.Date
AND (e.StartTime between inserted.StartTime and inserted.EndTime
OR e.EndTime between inserted.StartTime and inserted.EndTime)
AND h.HallsID = inserted.HallsID
AND e.StatusID in (2,3) -- booked, tentative
AND e.EventName <> inserted.EventName
if @[EMAIL PROTECTED]
is not null
begin
raiserror('This conflicts with the %s booked in the %s Banquet Hall on
%s between %s and %s',
16, 1, e.EventName, h.[Hall Name], e.Date, e.StartTime, e.EndTime)
rollback transaction
end
end
Note that this will always generate a false positive if you try to
change the EventName of an existing row.
|
