Trigger Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger Help

Author

Topic

aliqs
Starting Member

3 Posts

Posted - 2007-10-09 : 11:51:09

Hello, everyone. I need some assistance with a trigger problem.

This is a purchasing system with a web interface, which we did not write, so I cannot alter the code behind the web page and believe that my only solution is to use a trigger.

When the user adds a purchase order with a type = "PCARD" in Table A, they must add a new record in Table B for that PO, and a value goes into a field identifying whose PCARD they are working with. This all occurs through the delivered interface, by clicking a button to go to a new screen, entering the information for Table B, and clicking another button to return to the original entry screen for Table A. Nothing is saved yet. When the SAVE button is clicked, the trigger should fire and check this INSERTED record for Table A against the INSERTED record for Table B, then do a ROLLBACK TRANSACTION if Table B does not have the value in it.

So far I have the following, which of course does not work:

**********************************************************
CREATE TRIGGER PO_PCARD
ON TableA
AFTER INSERT, UPDATE AS
BEGIN
IF
(SELECT COUNT(*)
FROM inserted
WHERE po_type = 'PCARD'
<>
(SELECT COUNT(*)
FROM inserted
JOIN Table B
ON inserted.po_code = Table B.po_code
WHERE po_type = 'PCARD'
BEGIN
RAISERROR ('You MUST enter the appropriate USER information into the provided UDF field for PCARD Types.', 16, 1)
ROLLBACK TRANSACTION
END
END
**********************************************************

I am new to triggers, and have searched the web long and hard, but cannot find a solution when it has two tables involved. Maybe I should qualify that statement with "...a solution...that I can understand." ;)

Any assistance would be much appreciated.

Thanks,

Aliqs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 11:58:09

[code]CREATE TRIGGER PO_PCARD
ON TableA
AFTER INSERT, UPDATE AS
BEGIN
IF (SELECT COUNT(*) FROM inserted WHERE po_type = 'PCARD')
<>
(SELECT COUNT(*) FROM TableB INNER JOIN inserted AS i ON i.po_code = TableB.po_code WHERE i.po_type = 'PCARD')
BEGIN
RAISERROR ('You MUST enter the appropriate USER information into the provided UDF field for PCARD Types.', 16, 1)
ROLLBACK TRANSACTION
END
END[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 12:02:29

I'm struggling a bit with your COUNT(*) approach, this is what I would prefer - if I've understood your problem correctly. Does it help?


IF EXISTS
(
    SELECT *
    FROM   inserted AS I
    WHERE      I.po_type = 'PCARD'
           AND NOT EXISTS
           (
               SELECT *
               FROM TableB AS B
               WHERE B.po_code = I.po_code
                     AND ... compare any other MustHave fields between tables "I" and "B" ...
           )
)
BEGIN
	RAISERROR ('... error message ...', 16, 1)
	ROLLBACK TRANSACTION
END

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 12:03:32

Peso: I'm a bit worried that the COUNT(*) might have multiple hits - e.g. 2 one one record, and none on other, such that the totals still match

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 12:07:04

Yes.
It will help to see table layouts.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 12:08:49

Also OP's requirement to have INSERTED on TableA check against INSERTED on TableB can never be satisfied.
I wonder why he doesn't start a TRANSACTION to make sure there are records stored in both tables?

E 12°55'05.25"
N 56°04'39.16"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-09 : 12:09:17

I believe you can only rollback a transaction if it is encapsolated in "begin tran and "end tran". Find the stored procedure/s that add the PO, and then add the code there, I wouldn't do it in a trigger.

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 12:12:20

"I believe you can only rollback a transaction if it is encapsolated in "begin tran and "end tran""

SQL Server will start an implicit transaction (if none exists) when a Trigger fires. At least, that's my understanding of it

Peso: Get ready with "three strikes and you are out" please

but I don't think we ever have an Insert/Update without an Explicit transaction, so I may not actually have experienced it ...

OP can't change the code so adding the some code to application isn;t an option

Kristen

aliqs
Starting Member

3 Posts

Posted - 2007-10-09 : 12:39:53

Thanks for all your responses.

I tried the first 2, by Peso and Kristen. Same problem as I was having before in both cases...the record does not yet exist in Table B (because of the rollback?), so how can you check against Table B? What am I missing?

I understand the concern about using Count(*); there would never be multiple records on this particular transaction, but the other syntax is probably cleaner code. Thanks for that.

As for the rollback, I have it working in other triggers and it works great. Just never have tried to do it with this second table.

...is "OPs" a reference to me???

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 13:52:40

"the record does not yet exist in Table B (because of the rollback?)"

Don't think so, any rollback won't have happened when this code is launched.

How about you fie up SQL Profiler, then try a "Save" from the application, then see what SQL Profiler records.

Maybe the application is not inserting the TableA and TableB rows in the way you are expecting?

Kristen

aliqs
Starting Member

3 Posts

Posted - 2007-10-09 : 14:37:59

I would have to ask our DBA to run Profiler for me, but I would rather resolve this myself.

I am quite sure it IS the rollback removing the Table B transaction - I removed the rollback statement and tried an update on the PO. I received the error, but it created the Table B transaction; however, it would not let me save the Table A record. The table B transaction still exists.

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 14:51:01

If you left the RAISERROR in then you should have seen that. If you did NOT see the RAISERROR then it isn't the ROLLBACK as the two would appear together (i.e. if ROLLBACK was not commented out).

Without knowing what the application is ACTUALLY firing at the database I think we are working in the dark, unless someone else has a bright idea.

I can't help further unless you can give more info as to what is going on; time is precious ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources