URGENT HELP NEEDED! Simple Q on Triggers 
Author Message
 URGENT HELP NEEDED! Simple Q on Triggers

Ah, nevermind the first question. If I perform only step one, getdate() is
assigned to ALL rows.
Anyway, I'd better explain what I REALLY want.. I'm obviously in need of
more help:

I have 2 tables:

CREATE TABLE tab_types(
type   INT PRIMARY KEY NOT NULL,
untilwash  INT NULL,
untilinspection INT NULL,
untilextensive INT NULL,
new    BIT DEFAULT 0,
updated   DATETIME NULL)

CREATE TABLE tab_carriers(
type   INT FOREIGN KEY REFERENCES tab_types,
serial    INT NOT NULL,
passes   INT NULL,
lastwash  INT NULL,
lastinspection INT NULL,
lastextensive INT NULL,
updated   DATETIME NULL,
PRIMARY KEY(serial,type))

I have 2 triggers for each one of these:

CREATE TRIGGER tr_types ON tab_types
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
UPDATE tab_types
SET updated = getdate()
FROM tab_types
INNER JOIN inserted
ON tab_types.type=inserted.type
AND tab_types.serial=inserted.serial

CREATE TRIGGER tr_carriers ON tab_carriers
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
UPDATE tab_carriers
SET updated = getdate()
FROM tab_carriers
INNER JOIN inserted
ON tab_carriers.type=inserted.type
AND tab_carriers.serial=inserted.serial

Now, when the 'new' field in tab_types is 0, this works as I need it to, but
when the 'new' field in a record of the tab_types table is 1, the following
must be executed in the tr_types trigger:

UPDATE tab_carriers
SET lastwash=passes, lastinspection=passes, lastextensive=passes
WHERE type=<the type-field of the record in the tab_types that was updated
with new=1>

In other words, when a record in the tab_types is updated, and has new=1,
all tab_carriers records of that type is updates as shown above.

And now the 2nd part that is the trickiest:
When an update is done in the tab_carriers THIS WAY (meaning called from
within the tr_types) the updated field in tab_carriers should NOT be altered
and so tr_carriers whould NOT call updated=getdate()

In all other cases the triggers should work as they do now.
Briefly: when a type record is inserted/updated with new=1 then all carriers
of this type must be updated by setting their lastwash, lastinspection and
lastextensive equal to passes. But this update should not alter the updated
field as the tr_carriers trigger normally would.

Your help would be greatly appreciated by this newbie!

Fonzie



Fri, 25 Apr 2003 03:00:00 GMT  
 URGENT HELP NEEDED! Simple Q on Triggers
How about something like this:

CREATE TRIGGER tr_types ON tab_types
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
IF Inserted.new = 0
BEGIN
 UPDATE tab_types
 SET updated = getdate()
 FROM tab_types
 INNER JOIN inserted
 ON tab_types.type=inserted.type
 AND tab_types.serial=inserted.serial
 RETURN (0)
END

UPDATE tab_carriers
SET lastwash=passes, lastinspection=passes, lastextensive=passes
WHERE type=< inserted.type  /*shouldn't this be = (not =<)?*/
RETURN (0)
GO

Quote:
>>And now the 2nd part that is the trickiest:

I think that you can disallow triggers from firing other triggers.  This
'simple' solution will solve this problem.

my $0.02
Are you using stored procedures to access/update the data?  If so, I would
recommend that you modify the stored procedures to do the updates and get
rid of the triggers.  I find that stored procedures are much easier to use
and debug than embedded sql and triggers.

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.



Fri, 25 Apr 2003 03:00:00 GMT  
 URGENT HELP NEEDED! Simple Q on Triggers

In the first statement you are including inserted in the FROM clause via the
JOIN.

In the second statement you have no FROM statement, so to the statement
inserted does not exist.

Re # 2 look into the following in BOL

ALTER TABLE   DISABLE/ENABLE TRIGGER

Recursive Triggers

--
Doo
Senior Data Architect / DBA
PlanetJam Media Group


Quote:
> I can not reference inserted like you suggested. It will say inserted does
> not exist.
> Inserted is some kind of virtual table. I don't understand why I can't
> reference it like that either.. that's where I had a problem myself
> before... it works fine when inserted is used in the JOIN as you can see
in
> the original trigger function I had posted, but I can't use it anywhere
> else.
> You say I can disallow triggers from firing from other triggers.. if this
is
> true, I would like to know how.

> Fonzie



> >How about something like this:

> >CREATE TRIGGER tr_types ON tab_types
> >FOR INSERT,UPDATE
> >AS
> >SET NOCOUNT ON
> >IF Inserted.new = 0
> >BEGIN
> > UPDATE tab_types
> > SET updated = getdate()
> > FROM tab_types
> > INNER JOIN inserted
> > ON tab_types.type=inserted.type
> > AND tab_types.serial=inserted.serial
> > RETURN (0)
> >END

> >UPDATE tab_carriers
> >SET lastwash=passes, lastinspection=passes, lastextensive=passes
> >WHERE type=< inserted.type  /*shouldn't this be = (not =<)?*/
> >RETURN (0)
> >GO

> >>>And now the 2nd part that is the trickiest:
> >I think that you can disallow triggers from firing other triggers.  This
> >'simple' solution will solve this problem.

> >my $0.02
> >Are you using stored procedures to access/update the data?  If so, I
would
> >recommend that you modify the stored procedures to do the updates and get
> >rid of the triggers.  I find that stored procedures are much easier to
use
> >and debug than embedded sql and triggers.

> >--
> >Keith
> >==============
> >Please reply only to the newsgroups.
> >When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> >can be cut and pasted into Query Analyzer is appreciated.



Fri, 25 Apr 2003 03:00:00 GMT  
 URGENT HELP NEEDED! Simple Q on Triggers

I can not reference inserted like you suggested. It will say inserted does
not exist.
Inserted is some kind of virtual table. I don't understand why I can't
reference it like that either.. that's where I had a problem myself
before... it works fine when inserted is used in the JOIN as you can see in
the original trigger function I had posted, but I can't use it anywhere
else.
You say I can disallow triggers from firing from other triggers.. if this is
true, I would like to know how.

Fonzie


Quote:
>How about something like this:

>CREATE TRIGGER tr_types ON tab_types
>FOR INSERT,UPDATE
>AS
>SET NOCOUNT ON
>IF Inserted.new = 0
>BEGIN
> UPDATE tab_types
> SET updated = getdate()
> FROM tab_types
> INNER JOIN inserted
> ON tab_types.type=inserted.type
> AND tab_types.serial=inserted.serial
> RETURN (0)
>END

>UPDATE tab_carriers
>SET lastwash=passes, lastinspection=passes, lastextensive=passes
>WHERE type=< inserted.type  /*shouldn't this be = (not =<)?*/
>RETURN (0)
>GO

>>>And now the 2nd part that is the trickiest:
>I think that you can disallow triggers from firing other triggers.  This
>'simple' solution will solve this problem.

>my $0.02
>Are you using stored procedures to access/update the data?  If so, I would
>recommend that you modify the stored procedures to do the updates and get
>rid of the triggers.  I find that stored procedures are much easier to use
>and debug than embedded sql and triggers.

>--
>Keith
>==============
>Please reply only to the newsgroups.
>When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
>can be cut and pasted into Query Analyzer is appreciated.



Sat, 26 Apr 2003 07:00:39 GMT  
 URGENT HELP NEEDED! Simple Q on Triggers

BOL says I can not use ALTER TABLE from within a trigger... so your
suggestion unfortunately can't help me either. ;(

Fonzie

Quote:

>In the first statement you are including inserted in the FROM clause via
the
>JOIN.

>In the second statement you have no FROM statement, so to the statement
>inserted does not exist.

>Re # 2 look into the following in BOL

>ALTER TABLE   DISABLE/ENABLE TRIGGER

>Recursive Triggers

>--
>Doo
>Senior Data Architect / DBA
>PlanetJam Media Group



>> I can not reference inserted like you suggested. It will say inserted
does
>> not exist.
>> Inserted is some kind of virtual table. I don't understand why I can't
>> reference it like that either.. that's where I had a problem myself
>> before... it works fine when inserted is used in the JOIN as you can see
>in
>> the original trigger function I had posted, but I can't use it anywhere
>> else.
>> You say I can disallow triggers from firing from other triggers.. if this
>is
>> true, I would like to know how.

>> Fonzie



>> >How about something like this:

>> >CREATE TRIGGER tr_types ON tab_types
>> >FOR INSERT,UPDATE
>> >AS
>> >SET NOCOUNT ON
>> >IF Inserted.new = 0
>> >BEGIN
>> > UPDATE tab_types
>> > SET updated = getdate()
>> > FROM tab_types
>> > INNER JOIN inserted
>> > ON tab_types.type=inserted.type
>> > AND tab_types.serial=inserted.serial
>> > RETURN (0)
>> >END

>> >UPDATE tab_carriers
>> >SET lastwash=passes, lastinspection=passes, lastextensive=passes
>> >WHERE type=< inserted.type  /*shouldn't this be = (not =<)?*/
>> >RETURN (0)
>> >GO

>> >>>And now the 2nd part that is the trickiest:
>> >I think that you can disallow triggers from firing other triggers.  This
>> >'simple' solution will solve this problem.

>> >my $0.02
>> >Are you using stored procedures to access/update the data?  If so, I
>would
>> >recommend that you modify the stored procedures to do the updates and
get
>> >rid of the triggers.  I find that stored procedures are much easier to
>use
>> >and debug than embedded sql and triggers.

>> >--
>> >Keith
>> >==============
>> >Please reply only to the newsgroups.
>> >When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
>> >can be cut and pasted into Query Analyzer is appreciated.



Sat, 26 Apr 2003 14:46:53 GMT  
 URGENT HELP NEEDED! Simple Q on Triggers
Sorry about that.  I copied Fonzie's exmple and I forgot to add the join to
the inserted table.

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> In the first statement you are including inserted in the FROM clause via
the
> JOIN.

> In the second statement you have no FROM statement, so to the statement
> inserted does not exist.

> Re # 2 look into the following in BOL

> ALTER TABLE   DISABLE/ENABLE TRIGGER

> Recursive Triggers

> --
> Doo
> Senior Data Architect / DBA
> PlanetJam Media Group



Sat, 26 Apr 2003 03:00:00 GMT  
 URGENT HELP NEEDED! Simple Q on Triggers
Well, the first part of the problem seems to be solved by

CREATE TRIGGER tr_types ON tab_types
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
UPDATE tab_types
SET updated = getdate()
FROM tab_types
INNER JOIN inserted
ON tab_types.type=inserted.type
/* UPDATE ALL CARRIERS OF THIS TYPE */
UPDATE tab_carriers
SET
lastwash=passes,lastinspection=passes,lastextensive=passes,client=inserted.c
lient
FROM tab_carriers,inserted WHERE tab_carriers.type=inserted.type AND
inserted.new=1

The second and biggest problem is to keep the above from triggering:

CREATE TRIGGER tr_carriers ON tab_carriers
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
UPDATE tab_carriers
SET updated = getdate()
FROM tab_carriers
INNER JOIN inserted
ON tab_carriers.type=inserted.type
AND tab_carriers.serial=inserted.serial

Can't use ALTER TABLE tab_carriers DISABLE TRIGGER tr_carrieres, from within
another trigger ;(

Fonzie

Quote:

>Sorry about that.  I copied Fonzie's exmple and I forgot to add the join to
>the inserted table.

>--
>Keith
>==============
>Please reply only to the newsgroups.
>When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
>can be cut and pasted into Query Analyzer is appreciated.



>> In the first statement you are including inserted in the FROM clause via
>the
>> JOIN.

>> In the second statement you have no FROM statement, so to the statement
>> inserted does not exist.

>> Re # 2 look into the following in BOL

>> ALTER TABLE   DISABLE/ENABLE TRIGGER

>> Recursive Triggers

>> --
>> Doo
>> Senior Data Architect / DBA
>> PlanetJam Media Group



Sat, 26 Apr 2003 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. urgent simple help needed

2. Newbie needs urgent help, simple question

3. URGENT URGENT dlopen() help needed...

4. Urgent Help Needed-Calling the help file.

5. Help !! (urgent) a simple program

6. URGENT HELP with a simple problem: reading file into string

7. 3 Simple Questions PLEASE HELP URGENT

8. Help !! (urgent) a simple program

9. 3 Simple Questions PLEASE HELP URGENT

10. Need to trigger events for ActiveX controls

11. Urgent Need help

12. Need Urgent Help::::: Printer

 

 
Powered by phpBB® Forum Software