Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Trigger Expand / Collapse
Author
Message
Posted Monday, January 20, 2014 6:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:01 AM
Points: 81, Visits: 101
Hi,

Can anyone please help my with the following trigger.

ALTER TRIGGER [dbo].[PAT_2_AJS]

ON [dbo].[INVOICE_HEADER]

FOR INSERT
AS

BEGIN
EXECUTE sp_PAT_2_AJS_TEST
END
GO


When I execute the trigger it says command successful and when I execute Just the one line - EXECUTE sp_PAT_2_AJS_TEST it works,
but when I insert data through the front end the trigger doesn't work.
When I use the code in the SP instead of execute sp_PAT_2_AJS_TEST it also works..

Why is this?
Post #1532595
Posted Monday, January 20, 2014 6:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 40,390, Visits: 36,825
Define 'doesn't work'?

What does that procedure do? What's the trigger supposed to do? Why is the trigger not referencing inserted or deleted pseudo-tables at all?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1532598
Posted Monday, January 20, 2014 6:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:01 AM
Points: 81, Visits: 101
The SP inserts data into a table in another database.
The trigger should execute this stored procedure as soon as new data is inserted into the table.
Post #1532603
Posted Monday, January 20, 2014 6:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 40,390, Visits: 36,825
Ok, and...?

With the trigger as you have posted it, any insert will fire that trigger and hence will run that procedure. Whether it works as intended is another matter, but I can't tell that without knowing what the procedure does (specifics, not a one-line description) and what it's intended to do.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1532617
Posted Tuesday, January 28, 2014 6:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:01 AM
Points: 81, Visits: 101
The stored procedure works, because if I execute it outside the trigger, it works.

Maybe my syntax is wrong for the trigger or something
Post #1535412
Posted Tuesday, January 28, 2014 7:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 12,923, Visits: 32,284
crazy_new (1/28/2014)
The stored procedure works, because if I execute it outside the trigger, it works.

Maybe my syntax is wrong for the trigger or something


apparently, the stored procedure finds the data it's supposed to touch all by itself, since it has no parameters.

most likely, whatever code is inside that procedure should exist inside the trigger INSTEAD, and it should reference the INSERTED and DELETED virtual tables inside the trigger, as Gail suggested.

unless you post the actual code for your proc, we cannot help you.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1535438
Posted Tuesday, January 28, 2014 8:28 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:41 AM
Points: 674, Visits: 6,807
Lowell (1/28/2014)
crazy_new (1/28/2014)
The stored procedure works, because if I execute it outside the trigger, it works.

Maybe my syntax is wrong for the trigger or something


apparently, the stored procedure finds the data it's supposed to touch all by itself, since it has no parameters.

most likely, whatever code is inside that procedure should exist inside the trigger INSTEAD, and it should reference the INSERTED and DELETED virtual tables inside the trigger, as Gail suggested.

unless you post the actual code for your proc, we cannot help you.


+1
apparently, the stored procedure finds the data it's supposed to touch all by itself, since it has no parameters.

that is an interesting reaction related to new data.
You inserted a record, do something.
Insert a Date Time Stamp in the other table?
Post #1535490
Posted Tuesday, January 28, 2014 11:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:01 AM
Points: 81, Visits: 101
yes it does find all the data by itself, if I take the code and ut it into the trigger it works...but I need this trigger to execute a couple of stored procedures
Post #1535717
Posted Wednesday, January 29, 2014 12:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 40,390, Visits: 36,825
Can you post the stored proc code please? Without seeing that, there's not a chance in hell any of us are going to be able to guess why the trigger 'doesn't work'.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1535739
Posted Wednesday, January 29, 2014 12:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:01 AM
Points: 81, Visits: 101
BEGIN
INSERT INTO PAT_AJS.dbo.AJS_Client_Invoices

SELECT IL.INVOICE_ID, IH.Invocie_DATE,
IH.Case_ID,IH.Actor_ID,IL.Invoice_Line_Text ,IL.Invoice_Amount,B_L_SEQ_NUMBER,B_L_AMOUNT,BL.WORK_CODE_ID
FROM INVOICE_LINE IL, INVOICE_HEADER IH, BUDGET_LINE BL
WHERE IH.Invoice_ID = IL.Invoice_ID

AND IL.INVOICE_ID = (SELECT MAX(invoice_id)
FROM INVOICE_HEADER
WHERE INVOICE_ID > 9990000
AND INVOICE_ID LIKE '1%')
AND B_L_INVOICE_NUMBER = IH.INVOICE_ID


END

Post #1535745
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse