multiple inserted

I am writing a trigger on one table that has dependency on what is inserted
on a different table. so how can i grab the info that is inserted in one
table to use in a query in another trigger.
thanks.
seeker53 wrote:
> I am writing a trigger on one table that has dependency on what is
> inserted on a different table. so how can i grab the info that is
> inserted in one table to use in a query in another trigger.
> thanks.
Use the "inserted" virtual table from the insert trigger. It contains
all the rows that were inserted in the table in a DDL format that
matches the underlying table.
David Gugick
Imceda Software
www.imceda.com
|||Here is the existing trigger:
if exists(select count(b.begin_datetime)
from inserted a inner join tblevent b on a.event_sysid =
b.event_sysid
inner join tblparticipating_entity c on a.entity_id =
c.entity_id
where a.entity_type_code = 'c'
group by a.entity_type_code, b.begin_datetime
having count(b.begin_datetime) >1 and b.begin_datetime >=
'1/14/05')
begin
select @.errno = 30080,
@.errmsg = 'begin_datetime: Appointments can not be same day dated.'
goto error
end
this inserted will only give data inserted into the tblparticipating_entity
table but in the above query I also need the inserted value of begin_datetime
which is in the tblevent. As this trigger stands now data exists and so the
trigger is fired but I need the query to filter down to the inserted consumer
which is in the participating_entity inserted virtual table and also the
inserted begin_datetime which is in the tblevent inserted virtual table.
What I want the trigger to do is give an alert to a scheduler that a
consumer can not have more than one appointment in a day. Thanks.
"David Gugick" wrote:

> seeker53 wrote:
> Use the "inserted" virtual table from the insert trigger. It contains
> all the rows that were inserted in the table in a DDL format that
> matches the underlying table.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||On Fri, 14 Jan 2005 05:53:15 -0800, seeker53 wrote:

>Here is the existing trigger:
>if exists(select count(b.begin_datetime)
> from inserted a inner join tblevent b on a.event_sysid =
>b.event_sysid
> inner join tblparticipating_entity c on a.entity_id =
>c.entity_id
> where a.entity_type_code = 'c'
> group by a.entity_type_code, b.begin_datetime
> having count(b.begin_datetime) >1 and b.begin_datetime >=
>'1/14/05')
> begin
> select @.errno = 30080,
> @.errmsg = 'begin_datetime: Appointments can not be same day dated.'
> goto error
> end
Hi seeker53,
This trigger will always show the error message, regardless of the data
you actually tried to insert or update.
The subquery calculates a COUNT. If no single row matches the WHERE and
HAVING clauses of the subquery, the result of COUNT(..) will be 0 (the
number zero, not the letter ooh, nor the "absence symbol" NULL). So the
subquery will return 1 row (with the value 0 as data) and the exists
clause will be evaulated as true.
Another problem with your trigger code is that the date format 1/14/05 is
ambiguous. The only safe format for dates is yyyymmdd (20050114). Oh, and
hard coding todays date is not really a best practice either - unless you
really have a strong desire to go and manually edit this date every day
from now on!
I'd like to give you some suggestions how to rewrite this trigger, but to
tell you the truth, I have no idea what you're trying to accomplish, how
your tables look, etc. Please have a look at www.aspfaq.com/5006.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)