Empty 'inserted' table in insert trigger

I have a table with insert trigger. Inside the trigger, I
write records from the inserted table to another table.
However, I found that the inserted table is empty. Any Idea?

I am using ASE 12.5.0.3

Thanks in advance.
0
Vicky
9/21/2005 3:44:31 AM
sybase.ase.general 8655 articles. 0 followers. Follow

9 Replies
601 Views

Similar Articles

[PageSpeed] 10

not impossible. something like:

insert tbl
select * from other_tbl where 1=2

would fire the trigger, but not insert any rows, and have no corresponding
rows in the inserted table.

<Vicky> wrote in message news:4330d6dd.2d0a.1681692777@sybase.com...
> I have a table with insert trigger. Inside the trigger, I
> write records from the inserted table to another table.
> However, I found that the inserted table is empty. Any Idea?
>
> I am using ASE 12.5.0.3
>
> Thanks in advance.


0
Jeffrey
9/21/2005 4:55:22 AM
I have a statement like this:
insert tbl (col1, col2) values (val1, val2)
After the insert the record with val1 and val2 is found in
tbl but when I do a select count(*) from inserted in the
insert trigger, it returns 0.

> not impossible. something like:
>
> insert tbl
> select * from other_tbl where 1=2
>
> would fire the trigger, but not insert any rows, and have
> no corresponding rows in the inserted table.
>
> <Vicky> wrote in message
> > news:4330d6dd.2d0a.1681692777@sybase.com... I have a
> > table with insert trigger. Inside the trigger, I write
> > records from the inserted table to another table.
> However, I found that the inserted table is empty. Any
> Idea? >
> > I am using ASE 12.5.0.3
> >
> > Thanks in advance.
>
>
0
Vicky
9/21/2005 5:07:19 AM
Could you post your exact @@version, table DDL, trigger DDL,
and sample insert statement?

-bret

Vicky wrote:

> I have a statement like this:
> insert tbl (col1, col2) values (val1, val2)
> After the insert the record with val1 and val2 is found in
> tbl but when I do a select count(*) from inserted in the
> insert trigger, it returns 0.
>
> > not impossible. something like:
> >
> > insert tbl
> > select * from other_tbl where 1=2
> >
> > would fire the trigger, but not insert any rows, and have
> > no corresponding rows in the inserted table.
> >
> > <Vicky> wrote in message
> > > news:4330d6dd.2d0a.1681692777@sybase.com... I have a
> > > table with insert trigger. Inside the trigger, I write
> > > records from the inserted table to another table.
> > However, I found that the inserted table is empty. Any
> > Idea? >
> > > I am using ASE 12.5.0.3
> > >
> > > Thanks in advance.
> >
> >

0
Bret
9/21/2005 1:38:02 PM
@@version is:
Adaptive Server Enterprise/12.5.2/EBF 12060 ESD#2/P/NT
(IX86)/OS 4.0/ase1252/1844/32-bit/OPT/Thu Aug 12 00:13:38
2004

table:
create table duty_roster
( emp_no               int                 not null,
  left_date              datetime        not null,
  effective_from      datetime        not null,
  effective_to          datetime        default '9999-12-31'
not null,
  sch_type	             integer          null,
  depot_code           char(4)         null,
  route                    char(4)         null,
  repl_route             char(4)         null,
  run                       char(3)         null,
  shift                      char(1)         null,
  norm_ot                decimal(4, 2) null,
  night_duty             int                default 0    
not null,
  split_shift              int                 default 0   
not null,
  airbus                   int                 default 0   
not null,
  terminus_code       char(3)          null,
  duty_code             char(3)          null,
  acting_duty            int                default 0    not
null,
  sch_leave              char(31)        null,
  grade                    char(8)          null,
  job_code               char(4) default ''  null,
  mn                        char(8) default '' null,
  pc                         char(8) default '' null,
  ga                         char(8) default '' null,  	
  timestamp              timestamp    null,
  primary key (emp_no, left_date, effective_from) )
  lock datarows

trigger:
create trigger duty_roster_insert
    on duty_roster
    for insert
as
 if @@rowcount = 1
 begin

     insert into duty_roster_trigger_log (trans_datetime,
rowcnt)
     select getdate(), count(*) from inserted

     update duty_roster
          set d1.effective_to = d2.effective_to
        from inserted, duty_roster d1, duty_roster d2
      where d1.emp_no = inserted.emp_no and
                d1.left_date = inserted.left_date and
                d2.emp_no = inserted.emp_no and
                d2.left_date = inserted.left_date and
                d1.effective_from = inserted.effective_from
and
                d2.effective_from < inserted.effective_from
and
                d2.effective_to > inserted.effective_from

    if @@rowcount = 0 and
        (select count(*) from duty_roster d1, inserted
         where d1.emp_no = inserted.emp_no and
 	   d1.left_date = inserted.left_date ) > 1
     begin
       declare @first datetime
       select @first = min(d1.effective_from)
         from duty_roster d1, inserted
       where d1.emp_no = inserted.emp_no and
                 d1.left_date = inserted.left_date and
                 d1.effective_from <>
inserted.effective_from

       update duty_roster
            set d1.effective_to = dateadd(day, -1, @first)
          from inserted, duty_roster d1
        where d1.effective_from = inserted.effective_from
and
                  d1.emp_no = inserted.emp_no and
                  d1.left_date = inserted.left_date
     end

     update duty_roster
          set d1.effective_to =
                 dateadd(day,-1,inserted.effective_from)
        from inserted, duty_roster d1
      where d1.emp_no = inserted.emp_no and
                d1.left_date = inserted.left_date and
                d1.effective_from < inserted.effective_from
and
                d1.effective_to > inserted.effective_from
  end
  else
  begin
     print '!! multiple insertion not allowed !!'
     print 'action abort'
     rollback transaction
  end


The insert into duty_roster_log was added to the trigger for
debugging and I found that the rowcnt is 0 in the table.

Sample:
e.g. I have 2 rows with the following data:
emp_no      left_date          effective_from      
effective_to
---------      ----------          -----------------      
-------------
1001           9999-12-31      2005-9-1              
2005-9-15
1001           9999-12-31      2005-9-16            
9999-12-31

Then I have the following insert statement:

insert into duty_roster (emp_no, left_date, effective_from)
values (1001, '9999-12-31', '2005-10-1')

After the insert, record is found in the table, but the
effective_to for the 2005-9-16 record was not updated to
2005-9-30, its value was still 9999-12-31 and a new record
was inserted into table duty_roster_trigger_log with rowcnt
= 0.


> Could you post your exact @@version, table DDL, trigger
> DDL, and sample insert statement?
>
> -bret
>
> Vicky wrote:
>
> > I have a statement like this:
> > insert tbl (col1, col2) values (val1, val2)
> > After the insert the record with val1 and val2 is found
> > in tbl but when I do a select count(*) from inserted in
> > the insert trigger, it returns 0.
> >
> > > not impossible. something like:
> > >
> > > insert tbl
> > > select * from other_tbl where 1=2
> > >
> > > would fire the trigger, but not insert any rows, and
> > > have no corresponding rows in the inserted table.
> > >
> > > <Vicky> wrote in message
> > > > news:4330d6dd.2d0a.1681692777@sybase.com... I have a
> > > > table with insert trigger. Inside the trigger, I
> > > > write records from the inserted table to another
> > > table. However, I found that the inserted table is
> > > empty. Any Idea? >
> > > > I am using ASE 12.5.0.3
> > > >
> > > > Thanks in advance.
> > >
> > >
>
0
Vicky
9/22/2005 3:02:06 AM
Hi Vicky,

Well, I'm not seeing anything obvious.  Has the table always
been defined as datarows?  Triggers and procedures sometimes
don't behave well if the lock scheme of their tables is changed under
them - you might try dropping and recreating the trigger.

Does dbcc checktable / tablealloc run clean on both duty_roster
and duty_roster_trigger_log?

-bret


Vicky wrote:

> @@version is:
> Adaptive Server Enterprise/12.5.2/EBF 12060 ESD#2/P/NT
> (IX86)/OS 4.0/ase1252/1844/32-bit/OPT/Thu Aug 12 00:13:38
> 2004
>
> table:
> create table duty_roster
> ( emp_no               int                 not null,
>   left_date              datetime        not null,
>   effective_from      datetime        not null,
>   effective_to          datetime        default '9999-12-31'
> not null,
>   sch_type                   integer          null,
>   depot_code           char(4)         null,
>   route                    char(4)         null,
>   repl_route             char(4)         null,
>   run                       char(3)         null,
>   shift                      char(1)         null,
>   norm_ot                decimal(4, 2) null,
>   night_duty             int                default 0
> not null,
>   split_shift              int                 default 0
> not null,
>   airbus                   int                 default 0
> not null,
>   terminus_code       char(3)          null,
>   duty_code             char(3)          null,
>   acting_duty            int                default 0    not
> null,
>   sch_leave              char(31)        null,
>   grade                    char(8)          null,
>   job_code               char(4) default ''  null,
>   mn                        char(8) default '' null,
>   pc                         char(8) default '' null,
>   ga                         char(8) default '' null,
>   timestamp              timestamp    null,
>   primary key (emp_no, left_date, effective_from) )
>   lock datarows
>
> trigger:
> create trigger duty_roster_insert
>     on duty_roster
>     for insert
> as
>  if @@rowcount = 1
>  begin
>
>      insert into duty_roster_trigger_log (trans_datetime,
> rowcnt)
>      select getdate(), count(*) from inserted
>
>      update duty_roster
>           set d1.effective_to = d2.effective_to
>         from inserted, duty_roster d1, duty_roster d2
>       where d1.emp_no = inserted.emp_no and
>                 d1.left_date = inserted.left_date and
>                 d2.emp_no = inserted.emp_no and
>                 d2.left_date = inserted.left_date and
>                 d1.effective_from = inserted.effective_from
> and
>                 d2.effective_from < inserted.effective_from
> and
>                 d2.effective_to > inserted.effective_from
>
>     if @@rowcount = 0 and
>         (select count(*) from duty_roster d1, inserted
>          where d1.emp_no = inserted.emp_no and
>            d1.left_date = inserted.left_date ) > 1
>      begin
>        declare @first datetime
>        select @first = min(d1.effective_from)
>          from duty_roster d1, inserted
>        where d1.emp_no = inserted.emp_no and
>                  d1.left_date = inserted.left_date and
>                  d1.effective_from <>
> inserted.effective_from
>
>        update duty_roster
>             set d1.effective_to = dateadd(day, -1, @first)
>           from inserted, duty_roster d1
>         where d1.effective_from = inserted.effective_from
> and
>                   d1.emp_no = inserted.emp_no and
>                   d1.left_date = inserted.left_date
>      end
>
>      update duty_roster
>           set d1.effective_to =
>                  dateadd(day,-1,inserted.effective_from)
>         from inserted, duty_roster d1
>       where d1.emp_no = inserted.emp_no and
>                 d1.left_date = inserted.left_date and
>                 d1.effective_from < inserted.effective_from
> and
>                 d1.effective_to > inserted.effective_from
>   end
>   else
>   begin
>      print '!! multiple insertion not allowed !!'
>      print 'action abort'
>      rollback transaction
>   end
>
> The insert into duty_roster_log was added to the trigger for
> debugging and I found that the rowcnt is 0 in the table.
>
> Sample:
> e.g. I have 2 rows with the following data:
> emp_no      left_date          effective_from
> effective_to
> ---------      ----------          -----------------
> -------------
> 1001           9999-12-31      2005-9-1
> 2005-9-15
> 1001           9999-12-31      2005-9-16
> 9999-12-31
>
> Then I have the following insert statement:
>
> insert into duty_roster (emp_no, left_date, effective_from)
> values (1001, '9999-12-31', '2005-10-1')
>
> After the insert, record is found in the table, but the
> effective_to for the 2005-9-16 record was not updated to
> 2005-9-30, its value was still 9999-12-31 and a new record
> was inserted into table duty_roster_trigger_log with rowcnt
> = 0.
>
> > Could you post your exact @@version, table DDL, trigger
> > DDL, and sample insert statement?
> >
> > -bret
> >
> > Vicky wrote:
> >
> > > I have a statement like this:
> > > insert tbl (col1, col2) values (val1, val2)
> > > After the insert the record with val1 and val2 is found
> > > in tbl but when I do a select count(*) from inserted in
> > > the insert trigger, it returns 0.
> > >
> > > > not impossible. something like:
> > > >
> > > > insert tbl
> > > > select * from other_tbl where 1=2
> > > >
> > > > would fire the trigger, but not insert any rows, and
> > > > have no corresponding rows in the inserted table.
> > > >
> > > > <Vicky> wrote in message
> > > > > news:4330d6dd.2d0a.1681692777@sybase.com... I have a
> > > > > table with insert trigger. Inside the trigger, I
> > > > > write records from the inserted table to another
> > > > table. However, I found that the inserted table is
> > > > empty. Any Idea? >
> > > > > I am using ASE 12.5.0.3
> > > > >
> > > > > Thanks in advance.
> > > >
> > > >
> >

0
Bret
9/22/2005 3:22:50 PM
Minor detail but I'll ask it anyway ...

In the trigger the log table is named 'duty_roster_trigger_log', but in 
your comments you call the table 'duty_roster_log'.

Any chance both of these tables exist and either a) the trigger is 
inserting into the wrong table or b) you're looking at the wrong table?

During development it's easy to re-create a table with a new name but 
forget to drop the old table ... then forget to change all affected code 
to point to the new table.  So you're left watching the new table while 
the code is still writing to the old table.

Vicky wrote:

> @@version is:
> Adaptive Server Enterprise/12.5.2/EBF 12060 ESD#2/P/NT
> (IX86)/OS 4.0/ase1252/1844/32-bit/OPT/Thu Aug 12 00:13:38
> 2004
> 
> table:
> create table duty_roster
> ( emp_no               int                 not null,
>   left_date              datetime        not null,
>   effective_from      datetime        not null,
>   effective_to          datetime        default '9999-12-31'
> not null,
>   sch_type	             integer          null,
>   depot_code           char(4)         null,
>   route                    char(4)         null,
>   repl_route             char(4)         null,
>   run                       char(3)         null,
>   shift                      char(1)         null,
>   norm_ot                decimal(4, 2) null,
>   night_duty             int                default 0    
> not null,
>   split_shift              int                 default 0   
> not null,
>   airbus                   int                 default 0   
> not null,
>   terminus_code       char(3)          null,
>   duty_code             char(3)          null,
>   acting_duty            int                default 0    not
> null,
>   sch_leave              char(31)        null,
>   grade                    char(8)          null,
>   job_code               char(4) default ''  null,
>   mn                        char(8) default '' null,
>   pc                         char(8) default '' null,
>   ga                         char(8) default '' null,  	
>   timestamp              timestamp    null,
>   primary key (emp_no, left_date, effective_from) )
>   lock datarows
> 
> trigger:
> create trigger duty_roster_insert
>     on duty_roster
>     for insert
> as
>  if @@rowcount = 1
>  begin
> 
>      insert into duty_roster_trigger_log (trans_datetime,
> rowcnt)
>      select getdate(), count(*) from inserted
> 
>      update duty_roster
>           set d1.effective_to = d2.effective_to
>         from inserted, duty_roster d1, duty_roster d2
>       where d1.emp_no = inserted.emp_no and
>                 d1.left_date = inserted.left_date and
>                 d2.emp_no = inserted.emp_no and
>                 d2.left_date = inserted.left_date and
>                 d1.effective_from = inserted.effective_from
> and
>                 d2.effective_from < inserted.effective_from
> and
>                 d2.effective_to > inserted.effective_from
> 
>     if @@rowcount = 0 and
>         (select count(*) from duty_roster d1, inserted
>          where d1.emp_no = inserted.emp_no and
>  	   d1.left_date = inserted.left_date ) > 1
>      begin
>        declare @first datetime
>        select @first = min(d1.effective_from)
>          from duty_roster d1, inserted
>        where d1.emp_no = inserted.emp_no and
>                  d1.left_date = inserted.left_date and
>                  d1.effective_from <>
> inserted.effective_from
> 
>        update duty_roster
>             set d1.effective_to = dateadd(day, -1, @first)
>           from inserted, duty_roster d1
>         where d1.effective_from = inserted.effective_from
> and
>                   d1.emp_no = inserted.emp_no and
>                   d1.left_date = inserted.left_date
>      end
> 
>      update duty_roster
>           set d1.effective_to =
>                  dateadd(day,-1,inserted.effective_from)
>         from inserted, duty_roster d1
>       where d1.emp_no = inserted.emp_no and
>                 d1.left_date = inserted.left_date and
>                 d1.effective_from < inserted.effective_from
> and
>                 d1.effective_to > inserted.effective_from
>   end
>   else
>   begin
>      print '!! multiple insertion not allowed !!'
>      print 'action abort'
>      rollback transaction
>   end
> 
> 
> The insert into duty_roster_log was added to the trigger for
> debugging and I found that the rowcnt is 0 in the table.
> 
> Sample:
> e.g. I have 2 rows with the following data:
> emp_no      left_date          effective_from      
> effective_to
> ---------      ----------          -----------------      
> -------------
> 1001           9999-12-31      2005-9-1              
> 2005-9-15
> 1001           9999-12-31      2005-9-16            
> 9999-12-31
> 
> Then I have the following insert statement:
> 
> insert into duty_roster (emp_no, left_date, effective_from)
> values (1001, '9999-12-31', '2005-10-1')
> 
> After the insert, record is found in the table, but the
> effective_to for the 2005-9-16 record was not updated to
> 2005-9-30, its value was still 9999-12-31 and a new record
> was inserted into table duty_roster_trigger_log with rowcnt
> = 0.
> 
> 
> 
>>Could you post your exact @@version, table DDL, trigger
>>DDL, and sample insert statement?
>>
>>-bret
>>
>>Vicky wrote:
>>
>>
>>>I have a statement like this:
>>>insert tbl (col1, col2) values (val1, val2)
>>>After the insert the record with val1 and val2 is found
>>>in tbl but when I do a select count(*) from inserted in
>>>the insert trigger, it returns 0.
>>>
>>>
>>>>not impossible. something like:
>>>>
>>>>insert tbl
>>>>select * from other_tbl where 1=2
>>>>
>>>>would fire the trigger, but not insert any rows, and
>>>>have no corresponding rows in the inserted table.
>>>>
>>>><Vicky> wrote in message
>>>>
>>>>>news:4330d6dd.2d0a.1681692777@sybase.com... I have a
>>>>>table with insert trigger. Inside the trigger, I
>>>>>write records from the inserted table to another
>>>>
>>>>table. However, I found that the inserted table is
>>>>empty. Any Idea? >
>>>>
>>>>>I am using ASE 12.5.0.3
>>>>>
>>>>>Thanks in advance.
>>>>
>>>>
0
Mark
9/22/2005 3:57:04 PM
I've already dropped and re-create the table, still the same
behaviour.

> Hi Vicky,
>
> Well, I'm not seeing anything obvious.  Has the table
> always been defined as datarows?  Triggers and procedures
> sometimes don't behave well if the lock scheme of their
> tables is changed under them - you might try dropping and
> recreating the trigger.
>
> Does dbcc checktable / tablealloc run clean on both
> duty_roster and duty_roster_trigger_log?
>
> -bret
>
>
> Vicky wrote:
>
> > @@version is:
> > Adaptive Server Enterprise/12.5.2/EBF 12060 ESD#2/P/NT
> > (IX86)/OS 4.0/ase1252/1844/32-bit/OPT/Thu Aug 12
> > 00:13:38 2004
> >
> > table:
> > create table duty_roster
> > ( emp_no               int                 not null,
> >   left_date              datetime        not null,
> >   effective_from      datetime        not null,
> >   effective_to          datetime        default
> > '9999-12-31' not null,
> >   sch_type                   integer          null,
> >   depot_code           char(4)         null,
> >   route                    char(4)         null,
> >   repl_route             char(4)         null,
> >   run                       char(3)         null,
> >   shift                      char(1)         null,
> >   norm_ot                decimal(4, 2) null,
> >   night_duty             int                default 0
> > not null,
> >   split_shift              int                 default 0
> > not null,
> >   airbus                   int                 default 0
> > not null,
> >   terminus_code       char(3)          null,
> >   duty_code             char(3)          null,
> >   acting_duty            int                default 0
> > not null,
> >   sch_leave              char(31)        null,
> >   grade                    char(8)          null,
> >   job_code               char(4) default ''  null,
> >   mn                        char(8) default '' null,
> >   pc                         char(8) default '' null,
> >   ga                         char(8) default '' null,
> >   timestamp              timestamp    null,
> >   primary key (emp_no, left_date, effective_from) )
> >   lock datarows
> >
> > trigger:
> > create trigger duty_roster_insert
> >     on duty_roster
> >     for insert
> > as
> >  if @@rowcount = 1
> >  begin
> >
> >      insert into duty_roster_trigger_log (trans_datetime
> > , rowcnt)
> >      select getdate(), count(*) from inserted
> >
> >      update duty_roster
> >           set d1.effective_to = d2.effective_to
> >         from inserted, duty_roster d1, duty_roster d2
> >       where d1.emp_no = inserted.emp_no and
> >                 d1.left_date = inserted.left_date and
> >                 d2.emp_no = inserted.emp_no and
> >                 d2.left_date = inserted.left_date and
> >                 d1.effective_from =
> > inserted.effective_from and
> >                 d2.effective_from <
> > inserted.effective_from and
> >                 d2.effective_to >
> inserted.effective_from >
> >     if @@rowcount = 0 and
> >         (select count(*) from duty_roster d1, inserted
> >          where d1.emp_no = inserted.emp_no and
> >            d1.left_date = inserted.left_date ) > 1
> >      begin
> >        declare @first datetime
> >        select @first = min(d1.effective_from)
> >          from duty_roster d1, inserted
> >        where d1.emp_no = inserted.emp_no and
> >                  d1.left_date = inserted.left_date and
> >                  d1.effective_from <>
> > inserted.effective_from
> >
> >        update duty_roster
> >             set d1.effective_to = dateadd(day, -1,
> >           @first) from inserted, duty_roster d1
> >         where d1.effective_from =
> > inserted.effective_from and
> >                   d1.emp_no = inserted.emp_no and
> >                   d1.left_date = inserted.left_date
> >      end
> >
> >      update duty_roster
> >           set d1.effective_to =
> >                  dateadd(day,-1,inserted.effective_from)
> >         from inserted, duty_roster d1
> >       where d1.emp_no = inserted.emp_no and
> >                 d1.left_date = inserted.left_date and
> >                 d1.effective_from <
> > inserted.effective_from and
> >                 d1.effective_to >
> >   inserted.effective_from end
> >   else
> >   begin
> >      print '!! multiple insertion not allowed !!'
> >      print 'action abort'
> >      rollback transaction
> >   end
> >
> > The insert into duty_roster_log was added to the trigger
> > for debugging and I found that the rowcnt is 0 in the
> table. >
> > Sample:
> > e.g. I have 2 rows with the following data:
> > emp_no      left_date          effective_from
> > effective_to
> > ---------      ----------          -----------------
> > -------------
> > 1001           9999-12-31      2005-9-1
> > 2005-9-15
> > 1001           9999-12-31      2005-9-16
> > 9999-12-31
> >
> > Then I have the following insert statement:
> >
> > insert into duty_roster (emp_no, left_date,
> > effective_from) values (1001, '9999-12-31', '2005-10-1')
> >
> > After the insert, record is found in the table, but the
> > effective_to for the 2005-9-16 record was not updated to
> > 2005-9-30, its value was still 9999-12-31 and a new
> > record was inserted into table duty_roster_trigger_log
> > with rowcnt = 0.
> >
> > > Could you post your exact @@version, table DDL,
> > > trigger DDL, and sample insert statement?
> > >
> > > -bret
> > >
> > > Vicky wrote:
> > >
> > > > I have a statement like this:
> > > > insert tbl (col1, col2) values (val1, val2)
> > > > After the insert the record with val1 and val2 is
> > > > found in tbl but when I do a select count(*) from
> > > > inserted in the insert trigger, it returns 0.
> > > >
> > > > > not impossible. something like:
> > > > >
> > > > > insert tbl
> > > > > select * from other_tbl where 1=2
> > > > >
> > > > > would fire the trigger, but not insert any rows,
> > > > > and have no corresponding rows in the inserted
> > > > table. >
> > > > > <Vicky> wrote in message
> > > > > > news:4330d6dd.2d0a.1681692777@sybase.com... I
> > > > > > have a table with insert trigger. Inside the
> > > > > > trigger, I write records from the inserted table
> > > > > to another table. However, I found that the
> > > > > inserted table is empty. Any Idea? >
> > > > > > I am using ASE 12.5.0.3
> > > > > >
> > > > > > Thanks in advance.
> > > > >
> > > > >
> > >
>
0
Vicky
9/24/2005 3:58:48 AM
I just have a typo in my comment.

> Minor detail but I'll ask it anyway ...
>
> In the trigger the log table is named
> 'duty_roster_trigger_log', but in  your comments you call
> the table 'duty_roster_log'.
>
> Any chance both of these tables exist and either a) the
> trigger is  inserting into the wrong table or b) you're
> looking at the wrong table?
>
> During development it's easy to re-create a table with a
> new name but  forget to drop the old table ... then forget
> to change all affected code  to point to the new table.
> So you're left watching the new table while  the code is
> still writing to the old table.
>
> Vicky wrote:
>
> > @@version is:
> > Adaptive Server Enterprise/12.5.2/EBF 12060 ESD#2/P/NT
> > (IX86)/OS 4.0/ase1252/1844/32-bit/OPT/Thu Aug 12
> > 00:13:38 2004
> >
> > table:
> > create table duty_roster
> > ( emp_no               int                 not null,
> >   left_date              datetime        not null,
> >   effective_from      datetime        not null,
> >   effective_to          datetime        default
> > '9999-12-31' not null,
> >   sch_type                 integer          null,
> >   depot_code           char(4)         null,
> >   route                    char(4)         null,
> >   repl_route             char(4)         null,
> >   run                       char(3)         null,
> >   shift                      char(1)         null,
> >   norm_ot                decimal(4, 2) null,
> >   night_duty             int                default 0
> > not null,
> >   split_shift              int                 default 0
> >    not null,
> >   airbus                   int                 default 0
> >    not null,
> >   terminus_code       char(3)          null,
> >   duty_code             char(3)          null,
> >   acting_duty            int                default 0
> > not null,
> >   sch_leave              char(31)        null,
> >   grade                    char(8)          null,
> >   job_code               char(4) default ''  null,
> >   mn                        char(8) default '' null,
> >   pc                         char(8) default '' null,
> >   ga                         char(8) default '' null,
> >      timestamp              timestamp    null,
> >   primary key (emp_no, left_date, effective_from) )
> >   lock datarows
> >
> > trigger:
> > create trigger duty_roster_insert
> >     on duty_roster
> >     for insert
> > as
> >  if @@rowcount = 1
> >  begin
> >
> >      insert into duty_roster_trigger_log (trans_datetime
> > , rowcnt)
> >      select getdate(), count(*) from inserted
> >
> >      update duty_roster
> >           set d1.effective_to = d2.effective_to
> >         from inserted, duty_roster d1, duty_roster d2
> >       where d1.emp_no = inserted.emp_no and
> >                 d1.left_date = inserted.left_date and
> >                 d2.emp_no = inserted.emp_no and
> >                 d2.left_date = inserted.left_date and
> >                 d1.effective_from =
> > inserted.effective_from and
> >                 d2.effective_from <
> > inserted.effective_from and
> >                 d2.effective_to >
> > inserted.effective_from
> >     if @@rowcount = 0 and
> >         (select count(*) from duty_roster d1, inserted
> >          where d1.emp_no = inserted.emp_no and
> >         d1.left_date = inserted.left_date ) > 1
> >      begin
> >        declare @first datetime
> >        select @first = min(d1.effective_from)
> >          from duty_roster d1, inserted
> >        where d1.emp_no = inserted.emp_no and
> >                  d1.left_date = inserted.left_date and
> >                  d1.effective_from <>
> > inserted.effective_from
> >
> >        update duty_roster
> >             set d1.effective_to = dateadd(day, -1,
> >           @first) from inserted, duty_roster d1
> >         where d1.effective_from =
> > inserted.effective_from and
> >                   d1.emp_no = inserted.emp_no and
> >                   d1.left_date = inserted.left_date
> >      end
> >
> >      update duty_roster
> >           set d1.effective_to =
> >                  dateadd(day,-1,inserted.effective_from)
> >         from inserted, duty_roster d1
> >       where d1.emp_no = inserted.emp_no and
> >                 d1.left_date = inserted.left_date and
> >                 d1.effective_from <
> > inserted.effective_from and
> >                 d1.effective_to >
> >   inserted.effective_from end
> >   else
> >   begin
> >      print '!! multiple insertion not allowed !!'
> >      print 'action abort'
> >      rollback transaction
> >   end
> >
> >
> > The insert into duty_roster_log was added to the trigger
> > for debugging and I found that the rowcnt is 0 in the
> > table.
> > Sample:
> > e.g. I have 2 rows with the following data:
> > emp_no      left_date          effective_from
> > effective_to
> > ---------      ----------          -----------------
> >   -------------
> > 1001           9999-12-31      2005-9-1
> > 2005-9-15
> > 1001           9999-12-31      2005-9-16
> > 9999-12-31
> >
> > Then I have the following insert statement:
> >
> > insert into duty_roster (emp_no, left_date,
> > effective_from) values (1001, '9999-12-31', '2005-10-1')
> >
> > After the insert, record is found in the table, but the
> > effective_to for the 2005-9-16 record was not updated to
> > 2005-9-30, its value was still 9999-12-31 and a new
> > record was inserted into table duty_roster_trigger_log
> > with rowcnt = 0.
> >
> >
> >
> >>Could you post your exact @@version, table DDL, trigger
> >>DDL, and sample insert statement?
> >>
> >>-bret
> >>
> >>Vicky wrote:
> >>
> >>
> >>>I have a statement like this:
> >>>insert tbl (col1, col2) values (val1, val2)
> >>>After the insert the record with val1 and val2 is found
> >>>in tbl but when I do a select count(*) from inserted in
> >>>the insert trigger, it returns 0.
> >>>
> >>>
> >>>>not impossible. something like:
> >>>>
> >>>>insert tbl
> >>>>select * from other_tbl where 1=2
> >>>>
> >>>>would fire the trigger, but not insert any rows, and
> >>>>have no corresponding rows in the inserted table.
> >>>>
> >>>><Vicky> wrote in message
> >>>>
> >>>>>news:4330d6dd.2d0a.1681692777@sybase.com... I have a
> >>>>>table with insert trigger. Inside the trigger, I
> >>>>>write records from the inserted table to another
> >>>>
> >>>>table. However, I found that the inserted table is
> >>>>empty. Any Idea? >
> >>>>
> >>>>>I am using ASE 12.5.0.3
> >>>>>
> >>>>>Thanks in advance.
> >>>>
> >>>>
0
Vicky
9/24/2005 3:59:51 AM
Is it being updated through a stored procedure that was created in chained
mode, and used in unchained mode?

<Vicky> wrote in message news:4334cee5.4c8e.1681692777@sybase.com...
> I just have a typo in my comment.
>
> > Minor detail but I'll ask it anyway ...
> >
> > In the trigger the log table is named
> > 'duty_roster_trigger_log', but in  your comments you call
> > the table 'duty_roster_log'.
> >
> > Any chance both of these tables exist and either a) the
> > trigger is  inserting into the wrong table or b) you're
> > looking at the wrong table?
> >
> > During development it's easy to re-create a table with a
> > new name but  forget to drop the old table ... then forget
> > to change all affected code  to point to the new table.
> > So you're left watching the new table while  the code is
> > still writing to the old table.
> >
> > Vicky wrote:
> >
> > > @@version is:
> > > Adaptive Server Enterprise/12.5.2/EBF 12060 ESD#2/P/NT
> > > (IX86)/OS 4.0/ase1252/1844/32-bit/OPT/Thu Aug 12
> > > 00:13:38 2004
> > >
> > > table:
> > > create table duty_roster
> > > ( emp_no               int                 not null,
> > >   left_date              datetime        not null,
> > >   effective_from      datetime        not null,
> > >   effective_to          datetime        default
> > > '9999-12-31' not null,
> > >   sch_type                 integer          null,
> > >   depot_code           char(4)         null,
> > >   route                    char(4)         null,
> > >   repl_route             char(4)         null,
> > >   run                       char(3)         null,
> > >   shift                      char(1)         null,
> > >   norm_ot                decimal(4, 2) null,
> > >   night_duty             int                default 0
> > > not null,
> > >   split_shift              int                 default 0
> > >    not null,
> > >   airbus                   int                 default 0
> > >    not null,
> > >   terminus_code       char(3)          null,
> > >   duty_code             char(3)          null,
> > >   acting_duty            int                default 0
> > > not null,
> > >   sch_leave              char(31)        null,
> > >   grade                    char(8)          null,
> > >   job_code               char(4) default ''  null,
> > >   mn                        char(8) default '' null,
> > >   pc                         char(8) default '' null,
> > >   ga                         char(8) default '' null,
> > >      timestamp              timestamp    null,
> > >   primary key (emp_no, left_date, effective_from) )
> > >   lock datarows
> > >
> > > trigger:
> > > create trigger duty_roster_insert
> > >     on duty_roster
> > >     for insert
> > > as
> > >  if @@rowcount = 1
> > >  begin
> > >
> > >      insert into duty_roster_trigger_log (trans_datetime
> > > , rowcnt)
> > >      select getdate(), count(*) from inserted
> > >
> > >      update duty_roster
> > >           set d1.effective_to = d2.effective_to
> > >         from inserted, duty_roster d1, duty_roster d2
> > >       where d1.emp_no = inserted.emp_no and
> > >                 d1.left_date = inserted.left_date and
> > >                 d2.emp_no = inserted.emp_no and
> > >                 d2.left_date = inserted.left_date and
> > >                 d1.effective_from =
> > > inserted.effective_from and
> > >                 d2.effective_from <
> > > inserted.effective_from and
> > >                 d2.effective_to >
> > > inserted.effective_from
> > >     if @@rowcount = 0 and
> > >         (select count(*) from duty_roster d1, inserted
> > >          where d1.emp_no = inserted.emp_no and
> > >         d1.left_date = inserted.left_date ) > 1
> > >      begin
> > >        declare @first datetime
> > >        select @first = min(d1.effective_from)
> > >          from duty_roster d1, inserted
> > >        where d1.emp_no = inserted.emp_no and
> > >                  d1.left_date = inserted.left_date and
> > >                  d1.effective_from <>
> > > inserted.effective_from
> > >
> > >        update duty_roster
> > >             set d1.effective_to = dateadd(day, -1,
> > >           @first) from inserted, duty_roster d1
> > >         where d1.effective_from =
> > > inserted.effective_from and
> > >                   d1.emp_no = inserted.emp_no and
> > >                   d1.left_date = inserted.left_date
> > >      end
> > >
> > >      update duty_roster
> > >           set d1.effective_to =
> > >                  dateadd(day,-1,inserted.effective_from)
> > >         from inserted, duty_roster d1
> > >       where d1.emp_no = inserted.emp_no and
> > >                 d1.left_date = inserted.left_date and
> > >                 d1.effective_from <
> > > inserted.effective_from and
> > >                 d1.effective_to >
> > >   inserted.effective_from end
> > >   else
> > >   begin
> > >      print '!! multiple insertion not allowed !!'
> > >      print 'action abort'
> > >      rollback transaction
> > >   end
> > >
> > >
> > > The insert into duty_roster_log was added to the trigger
> > > for debugging and I found that the rowcnt is 0 in the
> > > table.
> > > Sample:
> > > e.g. I have 2 rows with the following data:
> > > emp_no      left_date          effective_from
> > > effective_to
> > > ---------      ----------          -----------------
> > >   -------------
> > > 1001           9999-12-31      2005-9-1
> > > 2005-9-15
> > > 1001           9999-12-31      2005-9-16
> > > 9999-12-31
> > >
> > > Then I have the following insert statement:
> > >
> > > insert into duty_roster (emp_no, left_date,
> > > effective_from) values (1001, '9999-12-31', '2005-10-1')
> > >
> > > After the insert, record is found in the table, but the
> > > effective_to for the 2005-9-16 record was not updated to
> > > 2005-9-30, its value was still 9999-12-31 and a new
> > > record was inserted into table duty_roster_trigger_log
> > > with rowcnt = 0.
> > >
> > >
> > >
> > >>Could you post your exact @@version, table DDL, trigger
> > >>DDL, and sample insert statement?
> > >>
> > >>-bret
> > >>
> > >>Vicky wrote:
> > >>
> > >>
> > >>>I have a statement like this:
> > >>>insert tbl (col1, col2) values (val1, val2)
> > >>>After the insert the record with val1 and val2 is found
> > >>>in tbl but when I do a select count(*) from inserted in
> > >>>the insert trigger, it returns 0.
> > >>>
> > >>>
> > >>>>not impossible. something like:
> > >>>>
> > >>>>insert tbl
> > >>>>select * from other_tbl where 1=2
> > >>>>
> > >>>>would fire the trigger, but not insert any rows, and
> > >>>>have no corresponding rows in the inserted table.
> > >>>>
> > >>>><Vicky> wrote in message
> > >>>>
> > >>>>>news:4330d6dd.2d0a.1681692777@sybase.com... I have a
> > >>>>>table with insert trigger. Inside the trigger, I
> > >>>>>write records from the inserted table to another
> > >>>>
> > >>>>table. However, I found that the inserted table is
> > >>>>empty. Any Idea? >
> > >>>>
> > >>>>>I am using ASE 12.5.0.3
> > >>>>>
> > >>>>>Thanks in advance.
> > >>>>
> > >>>>


0
Jeffrey
10/10/2005 8:15:48 PM
Reply:

Similar Artilces:

Is having a trigger that inserts a row in Table 'A', when a row in same table is inserted by ADo.Net code?
I want to insert a row for a Global user  in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a s...

DetailsView Insert Error: Cannot insert the value NULL into column 'customer', table 'DEMO
Using VWD Express edition creates a page(Default.aspx) with a Dropdownlist for selecting a customer and a GridView to show the details of the selected customer and to edit and delect a customer's detail. I add a button to link to page Add.aspx for add a new record. I add a DetailsView and the following code so it goes to New record mode when page loads:        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)               DetailsView1.ChangeMode(Det...

Error in Tutorial DetailsView"Cannot insert the value NULL into column 'contract', table 'pubs.dbo.authors'; column does not allow nulls. INSERT fails.
Hi,I am trying to do the insert part of detail's view Tutorial (the last one) and I received this error: "Cannot insert the value NULL into column 'contract', table 'pubs.dbo.authors'; column does not allow nulls. INSERT fails.Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'contract', table 'pubs.dbo.authors'; column does not allow nulls. INSER...

getting the error: Cannot insert the value NULL into column 'name', table 'InterGlobe.dbo.IG_Admin'; column does not allow nulls. INSERT fails. The statement has been terminated.
<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <script runat="server">   private void CrLogin_Click(object source, EventArgs e) {   try { //Response.Write("hello"); SqlDataSource2.Insert(); // Response.Redirect("logincreated.aspx"); } catch(Exception ex) { Response.Write(ex.Message); }   } </script>       <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">       &nbsp;<a...

update 'inserted' table in trigger
Hello All, i have some calculated fields while inserting. is there any way to update the 'inserted' table in trigger? or the only method is to write a stored-proc to do 'calcule then insert' Thanks. Alien Chen 05/14/2002 alien@sol.com.tw wrote... > Hello All, > > i have some calculated fields while inserting. > is there any way to update the 'inserted' table in trigger? > > or the only method > is to write a stored-proc to do 'calcule then insert' Within the trigger you would use the INSERTED table to f...

I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50.                 insert into Table values('arun's',20)  My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?    The direct answer to your question is: You insert it like this:   ...

How to insert 'empty' or 'default' values when SQL is first prepared and then executed
------_=_NextPart_001_01C6E1A5.2CB4F066 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable In my project I first prepare an INSERT statement 'INSERT INTO table (col1, col2, col3, ...) VALUES (?, ?, ?, ...)' once, then insert multiple records using 'execute(@values)'. The problem is that not all values of all columns are available. So I should either leave them empty or set them as default values. I try to use 'NULL' or 'DEFAULT' for those unavailable values in '@values', but it didn't work. ...

How to INSERT INTO [Table] ([Field]) VALUES('I Have a ' in value')
Hi, I want to INSERT INTO [Table] ([Field]) VALUES('I Have a ' in value') please teach me how to xxx INSERT INTO [Table] ([Field]) VALUES('I Have a '' in value') instead of one single quote, u need to add one more.Om Prakash Use Parameterized Queries ...you woudnt have to worry about escape characters besides saving your db from sqlinjection attacks. hth***********************Dinakar NethiLife is short. Enjoy it.***********************...

'Tab' N 'Insert'
Hello Friends, I want to insert a new row in my grid datawindow, whenever I press 'Tab' from the last column of the last row of my datawindow. Thanks Rahul Patodi In the user event mapped to the pbm_dwnkey event id put this: string ls_text long ll_row IF key = KeyTab! THEN IF THIS.GetColumn() = dw_1.Object.DataWindow.Column.Count THEN IF dw_1.GetRow() = dw_1.RowCount() THEN ll_row = dw_1.InsertRow(0) dw_1.ScrollToRow(ll_row) dw_1.SetRow(ll_row) END IF END IF END IF Rahul Patodi wrote: > Hello Friends, > I want to insert a new row i...

ObjectDataSource 'ObjectDataSource1' could not find a non-generic method 'Insert' that has parameters: Identity Column insertion issue
   Hi, I am using ObjectDatasource for Insert/Update/Delete record from the table. Both Update and Delete are working fine but only problem with Insertion, I have CMP_ID is my primary key(PK) and its 'Identity' coulum, so whenever new record created the PK will generate automatically. Below is my Code snaps. <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="Delete" InsertMethod="Insert" SelectMethod="GetData" OldValuesParameterFormatString="{0}" TypeName="CompanysDatasetTableAd...

''''''''''''''''''''
Name: haznen Email: haznenatyahoodotcom Product: Gran Paradiso Alpha 8 Summary: '''''''''''''''''''' Comments: '''''''''''''''''''''''''''''''''''' Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a1) Gecko/20061204 UGES/1.7.2.0 GranParadiso/3.0a1 From URL: http://www.mozilla.org/projects/granparadiso/ Note to readers: Hendrix gives...

Bugzilla 3.0 Setting 'Initial State' to 'ASSIGNED' does not insert record in bugs_activity table
We are trying to write some activity reports (and get information about timing of when the bug actually gets assigned), we found that when entering a new bug, and setting the 'Initial State' to 'ASSIGNED' instead of 'NEW', no record is inserted into the bugs_activity table. We need a way of determining when the bug was assigned for our metrics, and this functionality makes it inconsistent. If the bug is created the initial state of 'NEW', a record is inserted into the bugs_activity table when it gets 'ASSIGNED'. Does anybody have any ideas if ...

what does 'No overload for method 'Insert' takes '1' arguments' mean?
code that caused this error: line AddInBookSqlDataSource2.Insert(item);protected void inbookButton_Click(object sender, EventArgs e){ try{ AddInBookSqlDataSource1.Insert(); }catch (Exception ex){ uploadSPoneLabel.Text = "Saved Failed: SP One" + ex.Message; }foreach (ListItem item in authorsListBox5.Items){ try{ AddInBookSqlDataSource2.Insert(item); saveStatusLabel.Text = "Save Successful: SP Two"; }catch (Exception ex1){saveStatusLabel.Text = "Save Failed: SP Two" + ex1.Message; } } } any help appreciated Thanks in advance At a surface leve...

Insert into ASE11.9 writes '' as ' '!? Why?
We are using Sybase Adaptive Server Enterprise as Backend for our Microsoft Access 97 Application. Every '' (empty string) inserted into a varchar column results in a ' ' (string with one blank) value. This happens even if we insert the record by typing into SQL Advantage. Manfred Sorg msorg@swt.de This behaviour is documented, and has been this way for many years. Try the following for example: 1> select "a" + "" + "b" 2> go --- a b 1> select char_length("") 2> go ----------- 1 ...

Web resources about - Empty 'inserted' table in insert trigger - sybase.ase.general

Resources last updated: 12/7/2015 3:13:51 AM