IF EXISTS (SELECT * FROM dbo.SysObjects WHERE id = object_id(N'dbo.TRIGGERTRIGGER_ReservationsReferenceCode') AND OBJECTPROPERTY(id, N'IsTrigger') = 1) DROP TRIGGER dbo.TRIGGERTRIGGER_ReservationsReferenceCode 
GO
CREATE TRIGGER [dbo].[TRIGGERTRIGGER_ReservationsReferenceCode] ON [dbo].[RESERVATIONS] AFTER INSERT AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @ReferenceNumber INT
  DECLARE @GuestNumber INT
  DECLARE @UserIdNumber INT

  SELECT @UserIDNumber = UserIDNumber 
  FROM Personel WITH (NOLOCK) 
  WHERE UserTypeNumber = 10 -- utAutoService

  IF ISNULL(@UserIDNumber, 0) <= 0
  BEGIN
    SELECT @UserIDNumber = UserIDNumber 
    FROM Personel WITH (NOLOCK) 
    WHERE UserTypeNumber = 2 -- utSystem
  END

  IF ISNULL(@UserIDNumber, 0) <= 0
  BEGIN
    SELECT TOP 1 @UserIDNumber = UserIDNumber 
    FROM Personel WITH (NOLOCK) 
    ORDER BY UserIDNumber DESC
  END

  SELECT @ReferenceNumber = ReferenceNumber, @GuestNumber = GuestNumber 
  FROM inserted

  DECLARE @AReservationCode INT 
  EXEC ReservationCodeCreate @AReservationCode OUTPUT

  EXEC dbo.ReservationExternalRefCreate 'ReservationCode', @AReservationCode, @ReferenceNumber, @GuestNumber, @UserIdNumber
END