|
Home > Archive > ASE Database forum > October 2005 > Empty 'inserted' table in insert trigger
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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.
| |
| Jeffrey R. Garbus 2005-09-21, 3:24 am |
| 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.
| |
|
| 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
> However, I found that the inserted table is empty. Any
> Idea? >
>
>
| |
| Bret Halford 2005-09-21, 9:49 am |
| Could you post your exact @@version, table DDL, trigger DDL,
and sample insert statement?
-bret
Vicky wrote:
[color=darkred]
> 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.
>
| |
|
| @@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:
>
>
| |
| Bret Halford 2005-09-22, 11:24 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:
[color=darkred]
> @@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.
>
| |
| Mark A. Parsons 2005-09-22, 11:24 am |
| 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:
[color=darkred]
> @@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.
>
>
>
| |
|
| 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:
>
> inserted.effective_from >
> table. >
>
| |
|
| I just have a typo in my comment.
[color=darkred]
> 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:
>
| |
| Jeffrey R. Garbus 2005-10-27, 8:21 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...[color=darkred]
> I just have a typo in my comment.
>
|
|
|
|
|