newbie, @@error does not seem to work

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: dik mus (dik_at_remove.musit.nl)
Date: 01/05/05 

Date: Wed, 5 Jan 2005 20:37:52 +0100

Hi,
Someone left me with the unfinished script below.
The problem is that the if conditions does not seem to work as should,
because the @@error is always 0
Maybe there are linitations with the nesting of IF.. ? maybe something else
I dont know?

I am not very good in SQL script, so i would very much apriciate any help.
and i hope you dont mind looking at the quite long script.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO 

--
ALTER    TRIGGER [Insert to ca3k_scan] ON [dbo].[Event] AFTER INSERT
AS
Begin -- 01
-- Begin of Declaration section
Declare @SQL_ERROR int
Set @SQL_ERROR = 0
-- Declare variables to store values originating from the Event Table (And 
input values for the Event_Log Table)
Declare @Event_Edate datetime
Declare @Event_Number smallint
Declare @Event_Status smallint
Declare @Event_Badge bigint
Declare @Event_Class varchar (32)
Declare @Event_Description varchar (64)
Declare @Event_Name varchar (48)
Declare @Event_Sphere int
-- Declare variables to store values originating from the ca3k_Medew Table
Declare @ca3k_Medew_ltv_nr char (6)
Declare @ca3k_Medew_afd_nr char (3)
Declare @ca3k_Medew_pn_vl char (15)
Declare @ca3k_Medew_pn_tv char (7)
Declare @ca3k_Medew_pn_an char (30)
Declare @ca3k_Medew_aw_kaart char (9)
-- Declare variables to store values with destination the ca3k_Scan Table
Declare @ca3k_Scan_ltv_nr char (6)
Declare @ca3k_Scan_datum_in smalldatetime
Declare @ca3k_Scan_tijd_in char (8)
Declare @ca3k_Scan_datum_uit smalldatetime
Declare @ca3k_Scan_tijd_uit char (8)
Declare @ca3k_Scan_duur numeric(5,2)
Declare @ca3k_Scan_status_nr numeric(3,0)
Declare @ca3k_Scan_jaar char (4)
Declare @ca3k_Scan_week char (2)
Declare @ca3k_Scan_omschr char (30)
Declare @ca3k_Scan_deur_in char (6)
Declare @ca3k_Scan_deur_uit char (6)
-- Hulpvariabelen t.b.v. conversie datum van smalldatetime to char
Declare @Date_Field_Y char (4)
Declare @Date_Field_M char (2)
Declare @Date_Field_D char (2)
Declare @Date_Field_W char (2)
Declare @Time_Field_H char (2)
Declare @Time_Field_M char (2)
Declare @Time_Field_S char (2)
Declare @Date_Field char (10)
Declare @Time_Field char (10)
-- End of Declaration section
-- Fetch data from inserted
select  @Event_EDate = Inserted.EDate,
  @Event_Number = Inserted.Number,
  @Event_Status = Inserted.Status,
  @Event_Badge = Inserted.Badge,
   @Event_Class = Inserted.Class,
   @Event_Description = Inserted.Description,
   @Event_Name = Inserted.Name,
   @Event_Sphere = Inserted.Sphere
FROM Inserted
-- Convert @Event_Edate to @Date_Field and @Time_Field
Set @Date_Field_Y = (Cast(DATEPART(Year, @Event_EDate) AS varchar(4)))
Set @Date_Field_M = (Cast(DATEPART(Month , @Event_EDate) AS varchar(2)))
Set @Date_Field_D = (Cast(DATEPART(Day , @Event_EDate) AS varchar(2)))
set @Date_Field_W = (Cast(DATEPART(week , @Event_EDate) AS varchar(4)))
Set @Time_Field_H = (Cast(DATEPART(Hour, @Event_EDate) AS varchar(2)))
Set @Time_Field_M = (Cast(DATEPART(Minute , @Event_EDate) AS varchar(2)))
Set @Time_Field_S = (Cast(DATEPART(Second , @Event_EDate) AS varchar(2)))
if @Date_Field_D < 10
 Begin -- 02_1
  set @Date_Field_D = ('0' + @Date_Field_D)
  print @Date_Field_D
 End -- 02_1
if @Date_Field_M < 10
 Begin -- 02_1
  set @Date_Field_M = ('0' + @Date_Field_M)
  print @Date_Field_M
 End -- 02_1
Set @Date_Field = (@Date_Field_Y + @Date_Field_M + @Date_Field_D)
Set @Time_Field = (@Time_Field_H + ':' + @Time_Field_M + ':' + 
@Time_Field_S)
print 'Step 02'
print @Date_Field
print @Date_Field_W
print @Time_Field
print @Event_Number
print @Event_Status
print @Event_Badge
print @Event_Class
print @Event_Description
print @Event_Name
print @Event_Sphere
-- Check and evaluate data
if @Event_Description Like 'Badge%' AND
  (@Event_Name Like '%UIT-lezer' OR @Event_Name Like '%IN-lezer')
 Begin -- 03
  Select @ca3k_Medew_ltv_nr = ltv_nr From ca3k_Medew Where aw_kaart = 
@Event_Badge
  Set @SQL_ERROR = @@Error
  print 'Step 03'
  print @SQL_ERROR
  print @event_Description
  print @Event_Name
  print @Event_Badge
  print @ca3k_Medew_ltv_nr
  If (@SQL_ERROR <> 0)
   Begin -- 04
    SET @ca3k_Medew_ltv_nr = 'CA' & @Event_Badge -- Insert default LTVNummer
    print 'Step 04'
    print @SQL_ERROR
    print @event_Description
    print @Event_Name
    print @Event_Badge
     print @ca3k_Medew_ltv_nr
   End -- 04
  If @Event_Name Like '%In-lezer' -- Case port-type is "IN"
   Begin -- 05
    Select ltv_nr From ca3k_Scan where ltv_nr = @ca3k_Medew_ltv_nr And
          datum_in = @Date_Field AND tijd_uit = 0
    Set @SQL_ERROR = @@Error
     print 'Step 05'
     print @SQL_ERROR
     print @ca3k_Medew_ltv_nr
     print @Event_EDate
    If (@SQL_ERROR <> 0) -- There's no open in-klok record, Create new 
in-klok record
     Begin -- 06
      Insert Into ca3k_scan (ltv_nr, datum_in, tijd_in, datum_uit, tijd_uit, 
duur,
          status_nr, jaar, week, omschr, deur_in, deur_uit)
       values (@ca3k_Medew_ltv_nr,
        @Date_Field,
        @Time_Field,
        0,
        0,
        0,
        1,
        @Date_Field_Y,
        @Date_Field_W,
        @Event_Badge,
        @Event_Number,
        0)
      Set @SQL_ERROR = @@Error
       print 'Step 06'
       print @SQL_ERROR
       print @ca3k_Medew_ltv_nr
       print @Event_EDate
       print @Event_Badge
       print @Event_Number
      If (@SQL_ERROR <> 0)
       Begin -- 07
        Insert into Event_Log (EDate, Number, Status, Badge, Class, 
Description, Name,
            Sphere, TCC)
        values (@Event_EDate, @Event_Number, @Event_Status, @Event_Badge,
          @Event_Class, @Event_Description, @Event_Name,
          @Event_Sphere, 'IPCDIF')
        print 'Step 07'
        print @Event_EDate
        print @Event_Number
        print @Event_Status
        print @Event_Badge
        print @Event_Class
        print @Event_Description
        print @Event_Name
        print @Event_Sphere
        print 'IPCDIF'
       End -- 07
      Else
       Begin -- 08
        Insert into Event_Log (EDate, Number, Status, Badge, Class, 
Description, Name,
            Sphere, TCC)
        values (@Event_EDate, @Event_Number, @Event_Status, @Event_Badge,
          @Event_Class, @Event_Description, @Event_Name,
          @Event_Sphere, 'IPCDIS')
        print 'Step 08'
        print @Event_EDate
        print @Event_Number
        print @Event_Status
        print @Event_Badge
        print @Event_Class
        print @Event_Description
        print @Event_Name
        print @Event_Sphere
        print 'IPCDIS'
       End -- 08
     End -- 06
    Else -- There's an open in-klok record
     Begin -- 09
      Update ca3k_scan Set -- end-time = clock-time, Now there's no open 
in-klok record
        datum_uit = @Date_Field,
        tijd_uit = @Time_Field,
        Duur = 0,
        Status_nr = 0,
        deur_uit = @Event_Number
      where ltv_nr = @ca3k_Medew_ltv_nr
        And
         datum_in = @Date_Field
        And
        tijd_uit = 0
      Set @SQL_ERROR = @@Error
       print 'Step 09-1'
       print @SQL_ERROR
       print @ca3k_Medew_ltv_nr
       print @Event_EDate
       print @Event_Badge
       print @Event_Number
      If (@SQL_ERROR <> 0)
       Begin -- 10
        Insert into Event_Log (EDate, Number, Status, Badge, Class, 
Description, Name,
           Sphere, TCC)
        values (@Event_EDate, @Event_Number, @Event_Status, @Event_Badge,
          @Event_Class, @Event_Description, @Event_Name,
          @Event_Sphere, 'IPIDUF')
        print 'Step 10'
        print @Event_EDate
        print @Event_Number
        print @Event_Status
        print @Event_Badge
        print @Event_Class
        print @Event_Description
        print @Event_Name
        print @Event_Sphere
        print 'IPIDUF'
       End -- 10
      Else
       Begin -- 11
        Insert into Event_Log (EDate, Number, Status, Badge, Class, 
Description, Name,
           Sphere, TCC)
        values (@Event_EDate, @Event_Number, @Event_Status, @Event_Badge,
          @Event_Class, @Event_Description, @Event_Name,
          @Event_Sphere, 'IPIDUS')
        print 'Step 11'
        print @Event_EDate
        print @Event_Number
        print @Event_Status
        print @Event_Badge
        print @Event_Class
        print @Event_Description
        print @Event_Name
        print @Event_Sphere
        print 'IPIDUS'
       End -- 11
      Insert Into ca3k_scan  -- Now there's no open in-klok record, Create 
new in-klok record
         (ltv_nr, datum_in, tijd_in, datum_uit, tijd_uit, duur,
          status_nr, jaar, week, omschr, deur_in, deur_uit)
      values (@ca3k_Medew_ltv_nr,
        @Date_Field,
        @Time_Field,
        0,
        0,
        0,
        1,
        @Date_Field_Y,
        @Date_Field_W,
        @Event_Badge,
        @Event_Number,
        0)
      Set @SQL_ERROR = @@Error
       print 'Step 09-2'
       print @SQL_ERROR
       print @ca3k_Medew_ltv_nr
       print @Event_EDate
       print @Event_Badge
       print @Event_Number
      If (@SQL_ERROR <> 0)
       Begin -- 12
        Insert into Event_Log (EDate, Number, Status, Badge, Class, 
Description, Name,
            Sphere, TCC)
        values (@Event_EDate, @Event_Number, @Event_Status, @Event_Badge,
          @Event_Class, @Event_Description, @Event_Name,
          @Event_Sphere, 'IPIDIF')
        print 'Step 12'
        print @Event_EDate
        print @Event_Number
        print @Event_Status
        print @Event_Badge
        print @Event_Class
        print @Event_Description
        print @Event_Name
        print @Event_Sphere
        print 'IPIDIF'
       End -- 12
      Else
       Begin -- 13
        Insert into Event_Log (EDate, Number, Status, Badge, Class, 
Description, Name,
           Sphere, TCC)
        values (@Event_EDate, @Event_Number, @Event_Status, @Event_Badge,
          @Event_Class, @Event_Description, @Event_Name,
          @Event_Sphere, 'IPIDIS')
        print 'Step 13'
        print @Event_EDate
        print @Event_Number
        print @Event_Status
        print @Event_Badge
        print @Event_Class
        print @Event_Description
        print @Event_Name
        print @Event_Sphere
        print 'IPIDIS'
       End -- 13
     End -- 09
   End -- 05
  If @Event_Name Like '%Uit-lezer' -- Case port-type is "OUT"
   Begin -- 14
    Select ltv_nr From ca3k_Scan where ltv_nr = @ca3k_Medew_ltv_nr And
      datum_in = @Date_Field AND tijd_uit = 0
    Set @SQL_ERROR = @@Error
     print 'Step 14'
     print @SQL_ERROR
     print @ca3k_Medew_ltv_nr
     print @Event_EDate
    If (@SQL_ERROR <> 0) -- There's no open in-klok record so, regard 
clock-time as in-klok time and uit-klok time
     Begin -- 15
      Insert Into ca3k_scan (ltv_nr, datum_in, tijd_in, datum_uit, tijd_uit, 
duur,
         status_nr, jaar, week, omschr, deur_in, deur_uit)
      values (@ca3k_Medew_ltv_nr,
        @Date_Field,
        @Time_Field,
        @Date_Field,
        @Time_Field,
        0,
        1,
        @Date_Field_Y,
        @Date_Field_W,
        @Event_Badge,
        @Event_Number,
        0)
      Set @SQL_ERROR = @@Error
       print 'Step 15'
       print @SQL_ERROR
       print @ca3k_Medew_ltv_nr
       print @Event_EDate
       print @Event_Badge
       print @Event_Number
      If (@SQL_ERROR <> 0)
       Begin -- 16
        Insert into Event_Log (EDate, Number, Status, Badge, Class, 
Description, Name,
           Sphere, TCC)
        values (@Event_EDate, @Event_Number, @Event_Status, @Event_Badge,
          @Event_Class, @Event_Description, @Event_Name,
          @Event_Sphere, 'OPIDIF')
        print 'Step 16'
        print @Event_EDate
        print @Event_Number
        print @Event_Status
        print @Event_Badge
        print @Event_Class
        print @Event_Description
        print @Event_Name
        print @Event_Sphere
        print 'OPIDIF'
       End -- 16
      Else
       Begin -- 17
        Insert into Event_Log (EDate, Number, Status, Badge, Class, 
Description, Name,
           Sphere, TCC)
        values (@Event_EDate, @Event_Number, @Event_Status, @Event_Badge,
          @Event_Class, @Event_Description, @Event_Name,
          @Event_Sphere, 'OPIDIS')
        print 'Step 17'
        print @Event_EDate
        print @Event_Number
        print @Event_Status
        print @Event_Badge
        print @Event_Class
        print @Event_Description
        print @Event_Name
        print @Event_Sphere
        print 'OPIDIS'
       End -- 17
     End -- 15
    Else -- There's an open in-klok record
     Begin -- 18
      Update ca3k_scan Set -- end-time = clock-time, Now there's no open 
in-klok record
         datum_uit = @Date_Field,
         tijd_uit = @Time_Field,
         duur = 0,
         status_nr = 0,
         deur_uit = @Event_Number
      where ltv_nr = @ca3k_Medew_ltv_nr
        And
         datum_in = @Date_Field
        And
        tijd_uit = 0
      Set @SQL_ERROR = @@Error
       print 'Step 18'
       print @SQL_ERROR
       print @ca3k_Medew_ltv_nr
       print @Event_EDate
       print @Event_Number
      If (@SQL_ERROR <> 0)
       Begin -- 19
        Insert into Event_Log (EDate, Number, Status, Badge, Class, 
Description, Name,
           Sphere, TCC)
        values (@Event_EDate, @Event_Number, @Event_Status, @Event_Badge,
          @Event_Class, @Event_Description, @Event_Name,
          @Event_Sphere, 'OPCDUF')
        print 'Step 19'
        print @Event_EDate
        print @Event_Number
        print @Event_Status
        print @Event_Badge
        print @Event_Class
        print @Event_Description
        print @Event_Name
        print @Event_Sphere
        print 'OPCDUF'
       End -- 19
      Else
       Begin -- 20
        Insert into Event_Log (EDate, Number, Status, Badge, Class, 
Description, Name,
           Sphere, TCC)
        values (@Event_EDate, @Event_Number, @Event_Status, @Event_Badge,
          @Event_Class, @Event_Description, @Event_Name,
          @Event_Sphere, 'OPCDUS')
        print 'Step 20'
        print @Event_EDate
        print @Event_Number
        print @Event_Status
        print @Event_Badge
        print @Event_Class
        print @Event_Description
        print @Event_Name
        print @Event_Sphere
        print 'OPCDUS'
       End -- 20
     End -- 18
 End -- 03
End -- 01
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Relevant Pages

  • Re: C string array problems (again)
    ... on the char ** var approach. ... scope and being re-initialized with each loop. ... So why did they declare it ... Would have to take a look a the original code to see what was meant. ...
    (microsoft.public.vb.general.discussion)
  • Re: Cursor and Procedure
    ... I need to truncate those strings to the segments with 200 ... Actually problem is ntext hold> 4000 char. ... DECLARE @deptTriDes ntext ...
    (comp.databases.ms-sqlserver)
  • Re: Comments on my code?
    ... value in a char *. ... Since you didn't declare the argument type, ... void *malloc; ... There's simply no good reason not to ...
    (comp.lang.c)
  • Re: Question about unpacking a binary file: endian troubles
    ... > This may be a dumb question; I'm just getting into C language here. ... `char' is always zero or positive, ... include to declare it. ... > int fdi, n; ...
    (comp.lang.c)
  • DateTime Variable in Where Cluase Slows SP to a Crawl
    ... DECLARE @sTime int ... DECLARE @eTime int ... DECLARE @eDate varchar ...
    (microsoft.public.sqlserver.programming)