MS SQL Server :: Trigger Firing Multiple Stored Procedure

Firing A Trigger When A User Updates Data But Not When A Stored Procedure Updates Data
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:

Code Block

create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.

For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.

If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.

How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?

View Replies !

Firing A Java Application From Stored Procedure
Hey all,
I've got a question and after doing some research I've found only a vague reference but no clear answer.

I have a java app that will be passing parameters to my stored procedure. I'll grab the requested info from the tables but instead of sending it back to the java app that sent the request, I need to send it to a "different" java app (the second java app will not be running at the time).

Can someone point me to a good source for executing java applications from a stored procedure?

Thanks in advance ...
tam

View Replies !

Firing An Append Query(stored Procedure) On The First Of Very Month
I need several stored procedures to fire on the 1st of every month. I was told my another developer I can possibly use SQL sever agent.

Any ideas????

View Replies !

Receiving Queue Stops Firing Stored Procedure

I've set up a Service Broker and it was working fine.

All of a sudden each time I switch on my computer it's not working anymore.

I have to drop the services, queues, contract and messages and create them again.

Then it works again until I shutdown my computer. When I start it again it's not working.

Any suggestion on how to fix this?

Thank you

View Replies !

Trigger Firing And Not Firing
I have a trigger on a table that just updates a last_modified_date and this works fine on our production server. Now I have to update some data and I do not want the trigger to fire. I cannot disable or drop the trigger because the productions systems needs the trigger. Anyone an idea of how to solve this problem?

View Replies !

Trigger Not Firing
OK, I'm at a loss..it must be staring me right in the face.

I have a junction table that relates 2 tables, with a unique key of the composit of the 2 keys. There is also an indicator that says 1 relationship between the 2 tables is "primary" and there should only be one of those. So I figured a trigger to take care of it...but I can't seem to get it working...I wrote sample sql to mimic the inserted table as well, and it seems correct, but the trigger just does not fire.

Any ideas?

CREATE TABLE [dbo].[PIF_MEP99] (
[PIFRecID] [int] NOT NULL ,
[MEPRecID] [int] NOT NULL ,
[PrimaryInd] [char] (1) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PIF_MEP99] WITH NOCHECK ADD
CONSTRAINT [PIF_MEP99_PK] PRIMARY KEY CLUSTERED
(
[PIFRecID],
[MEPRecID]
) ON [PRIMARY]
GO

INSERT INTO PIF_MEP99(PIFRecID, MEPRecID, PrimaryInd)
SELECT 1,1,'Y' UNION ALL
SELECT 2,1,'N' UNION ALL
SELECT 3,2,'N'
GO

CREATE TRIGGER dbo_PIF_MEP99_tr_Rule1 ON dbo.PIF_MEP99
FOR UPDATE, DELETE
AS
SET NOCOUNT ON

-- Rule 1: Prevent and MEP from having more than 1 PIF as Primary

IF Exists ( SELECT * FROM inserted i
INNER JOIN PIF_MEP99 p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y')
BEGIN
ROLLBACK TRAN
RAISERROR 500003 'Attempting to Insert 2 Primary PIFs for an MEP'
END
GO

SELECT * FROM PIF_MEP99
GO

SELECT * FROM (SELECT 4 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i
INNER JOIN PIF_MEP p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y'
GO

BEGIN TRAN
INSERT INTO PIF_MEP99 (PIFRecID, MEPRecID, PrimaryInd) SELECT 4,1,'Y'
COMMIT TRAN
GO

SELECT * FROM PIF_MEP99
GO

SELECT * FROM (SELECT 5 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i
INNER JOIN PIF_MEP p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y'
GO

DROP TABLE PIF_MEP99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

View Replies !

Trigger Not FIRING With BCP
Hi,

Anyone got an idea why this does not work. I have a trigger that is supposed to fire as an INSERT is done on a table. If I manually insert (insert into....), the trigger fires. If I use BCP to insert, the trigger DOES NOT FIRE.....

Davy

View Replies !

Trigger Not Firing ...
Afternoon All ...

I have a trigger on a table that works great when a single record is updated BUT it seems not to fire when I try to do a bulk update as in ...

update TableA

set field3 = ms.field3
FROM TableA as ta,
#ms as ms
where field1 = ms.field1
and field2 = ms.field2

Why does this happen and how can I correct it.

Thanks!

View Replies !

Trigger Not Firing

I have build a SQL Trigger that fires on the update of a specific column; this works perfectly when I test using SQL or even the SQL Server GUI but when I do the update from .NET it doesn€™t fire.

I have a dataset that gets modified and then I call sqlDataAdapter.Update(Dataset) - the row is successfully modified in the database but alas - the trigger isn't fired.

Any help would be appreciated.

View Replies !

Trigger Not Firing On Update
hi,Here's the scenario1) I am running a DTS job to fetch some rows from Oracle2) The job populates the Table A as step 13) Then it fires a update statement which updates the rows in Table B.Here's the statementUPDATE Table B SETtime = case when (select median from Table A where sno = sno and TableA.stno=70 ) is null then timeelse (select median from Table A where Table B.sno = Table A.sno andTable A.sstno=70) end ,endWHERE EXISTS (select sstno from Table A where Table B.sno = TableA.sno)There is a trigger on table B which should fire as soon as thevalue>15.When I fire the update statement direcly with a higher value than itfires the trigger.update table B set time=17 where b.sno=1000But not when the job runs...I am puzzled.Thoughts?AJ

View Replies !

Trigger Not Firing, DTS Load
I have a table that is getting refreshed from DB2 using DTS (I believe the DBA is doing a DELETE and an APPEND). I have a trigger on this table ON APPEND, INSERT, but the trigger never fires. When I manually update the data, the trigger fires no problem...

Is DTS capable of updating a SQL Server table without firing the trigger?

I'm an Oracle guy, and this is my 1st experiences with SQL Server, so I'll put the code here and if you want to point out any bad practices (such as the way i converted the DB2 TIMESTAMP to a SQL Server DATETIME , please do.

FYI, the DB2 TIMESTAMP is getting loaded into the SQL Server table as a VARCHAR(26)
Carl

CREATE TRIGGER trig_SAWakeUp ON tsnro
FOR INSERT, UPDATE
AS
DECLARE @snro_stus char(10)
DECLARE @snp_sht_dtm as datetime
SELECT @snro_stus = snro_stus
FROM tsnro
IF (RTRIM(@snro_stus) = 'ASSIGNED') OR (RTRIM(@snro_stus) = 'REFRESHED')
BEGIN
SELECT @snp_sht_dtm = CONVERT(DATETIME, SUBSTRING(evt_dtm,1,19))
FROM tsnro

INSERT INTO TSNP_SHT_DTM (SNRO_STUS, SNP_SHT_DTM )
VALUES (@snro_stus, @snp_sht_dtm)
END
ELSE
BEGIN
INSERT INTO TSNP_SHT_DTM ( SNRO_STUS, SNP_SHT_DTM )
VALUES (@snro_stus, @snp_sht_dtm)
END

View Replies !

Insert Trigger Sometimes Not Firing
hi all

i have an issue with an insert trigger sometimes not firing.

here is the trigger

CREATE TRIGGER Insert_tPABillToAddr ON [dbo].[tPA00175]
FOR INSERT
AS

INSERT into tPABillToAddr
(
chrJobNumber
)

SELECT chrJobNumber
FROM inserted

when the user enters a new this table is to insert one column into another table. the thing is, sometimes it does not do the insert. any ideas as to why? it is a very uncommon thing, lets say once out of every 20 inserts does it fail. but it is crucial that it never fails.

thanks

View Replies !

Prevent Trigger From Firing
Hi,

I have an update tigger on one of my tables.
I want to fire an Update SQL but somehow prevent trigger from firing.

Any Ideas..

View Replies !

Artificially Firing A TRIGGER
I have a table (table 1) that has a trigger attached to it, which sends data to table 2. Both tables are attached to an appliction.

In theory, the trigger populates table 2 (from table 1) from the data entered from the user interface application.

But when I import data from the backend

via DTS into Table 1, the trigger does not send the data to table 2.

What do I need to do ???

Thank you in advance !!

View Replies !

Update Trigger Not Firing.

Hi All,

I have an update trigger which stores the date a record was updated by a GIS app.

It worked in SQL2000 but when I copied and pasted the code into SQL2 its not firing. Any Ideas? Below is the code.

Create TRIGGER [dbo].[Date_Entered] ON [dbo].[ZONING_OP]

FOR UPDATE

AS

UPDATE [dbo].[ZONING_OP]

Set Date_Entered = GetDate()

Where ID = (Select ID from Inserted)

View Replies !

AFTER INSERT Trigger Not Firing In SQL 2005
Nothing fancy; just a trigger on a sharepoint table that supposed towrite a record to another SQL table.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER TRIGGER [TV_UpdateFileSyncProgress]ON [dbo].[Docs]AFTER INSERTASBEGINSET NOCOUNT ON;BEGINIF EXISTS (SELECT null FROM inserted WHERE DirName like'csm/%/Shared Documents')BEGINIF NOT EXISTS (SELECT null FROM inserted INNER JOINTV_FileSyncProgress fp ON LOWER(RTRIM(fp.LeafName)) =LOWER(RTRIM(Replace(Replace(inserted.DirName,'csm/',''),'/SharedDocuments','') + '' + inserted.LeafName)))BEGININSERT INTO TV_FileSyncProgress (InternalOrigin, ExternalOrigin,ChangeType, SiteId, DirName, LeafName, FlagForDelete)SELECT0,1,1,SiteId,'F:commonExtranet',Replace(Replace (DirName,'csm/',''),'/SharedDocuments','') + '' + LeafName,0 FROM insertedENDENDENDEND

View Replies !

Create Trigger Not Firing Via ODBC
I created a trigger in the "source table" that will "feed" and secondtable. The trigger is as follows:CREATE TRIGGER [FeedToP21] ON dbo.FromUPSFOR INSERTASDeclare @Count intSelect @Count = Count(*) from InsertedIf @Count > 0BeginInsert into ToP21Select i.* From Inserted iLeft Join ToP21 ton i.recnum = t.recnumWhere t.recnum is nullEndIf @@ERROR != 0Rollback TranA record was created in the "source table" via ODBC, however, thetrigger does not seem to have fired to create the record in the secondtable.If I create a record manually using SQL Server Enterprise Managerwithin the "tableview" the trigger fires and a duplicate record iscreated in the second table.Is there a fix for this problem?Thank you in advance.

View Replies !

UPDATE Trigger Firing Too Late?
Hi. This SHOULD be something simple, but I am apparently missing something. I have a Users table where a user's status is a varchar(100). I'm trying to implement a trigger so that when a user's status is changed to any string other than what it was before the update the trigger would change the LastUpdated field to current date/time.

Here's the trigger, I replaced the update of LastUpdated with a simple print statement. For some reason, it seems like the trigger is firing after the update statement has committed because the values of @m_status_new and @m_status_old are always the same so this trigger always prints 'status has not changed.' What am I doing wrong? Thank you greatly for any help provided.

USE myDB
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'tr_update_users' AND type = 'TR')
DROP TRIGGER tr_update_users
GO

CREATE TRIGGER tr_update_users
on dbo.Users
FOR UPDATE
AS

DECLARE @m_status_new varchar(100),
@m_status_old varchar(100),
@m_UserID int

SELECT @m_status_new = UserStatus,
@m_UserID = UserID
FROMinserted

SELECT @m_status_old = UserStatus
FROMUsers
WHEREUserID = @m_UserID

IF @m_status_new != @m_status_old
BEGIN
print 'status has changed'
END
ELSE
BEGIN
print 'status has not changed'
END
GO

View Replies !

UPDATE TRIGGER WITHOUT RECURSIVE FIRING
I have an update trigger I created that updates a field based on the user who last updated the record. Under 7 the only way it would work was to have recursive triggers firing turned on. Under 2000 might there be a btter solution. The code is below. Thanks

CREATE trigger tr_cmsUpdt_MARS on dbo.PATIENT_MEDICATION_DISPERSAL_ for UPDATE as
-- updates record with sql user and timestamp
--created 11-28-00 tim cronin
DECLARE @muser varchar(35),
@rec_lock_status int,
@ptacpt_status int
set @muser = current_user
begin
UPDATE PATIENT_MEDICATION_DISPERSAL_
set MODIFIED_BY = @muser,
MODIFIED_TS = getdate()
from deleted dt
WHERE --DT.MODIFIED_BY <> 'DBO' AND
PATIENT_MEDICATION_DISPERSAL_.RECORD_ID = dt.RECORD_ID
end

View Replies !

Error W/ Trigger When Firing On 'DELETE XYZ WHERE IN (1,2,3)'
Hi,

Thanks for looking at this post. I currently have a trigger that fires when a row is inserted or deleted on a table. The idea behind the trigger is that when a row is inserted (representing a sub-category for images), the categories parent needs to have some work done on it. I currently have the trigger working just fine with single inserts and single deletes:

sql Code:

Original
- sql Code

CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_sync
ON smvcModImageManagerCategory
FOR INSERT, DELETE
AS
DECLARE @pdfId INTEGER;
DECLARE @parentPdfId INTEGER;
DECLARE @grandparentPdfId INTEGER;
DECLARE @parentId INTEGER;
DECLARE @grandparentId INTEGER;
DECLARE @tableName VARCHAR( 255 );

-- If I am being inserted or deleted, and I am not a top level
-- category, then my parent's pdf record needs to be set so that
-- the pdf file is updated
IF (SELECT id FROM Inserted) IS NOT NULL
BEGIN
SELECT @pdfId = (SELECT pdfManagerId FROM Inserted),
@parentId = (SELECT parentCategory FROM Inserted),
@grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory
WHERE id = @parentId);
END
ELSE
BEGIN
SELECT @pdfId = (SELECT pdfManagerId FROM Deleted),
@parentId = (SELECT parentCategory FROM Deleted),
@grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory
WHERE id = @parentId)
END

-- If I am not a top level category, set my parent's pdf to be
-- updated
IF @parentId <> -1
BEGIN
SELECT @parentPdfId = (SELECT pdfManagerId
FROM smvcModImageManagerCategory
WHERE id = @parentId);
UPDATE smvcModPdfManager SET data_last_updated = GETDATE()
WHERE id = @parentPdfId;
END
GO

CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_syncON smvcModImageManagerCategoryFOR INSERT, DELETEAS DECLARE @pdfId INTEGER; DECLARE @parentPdfId INTEGER; DECLARE @grandparentPdfId INTEGER; DECLARE @parentId INTEGER; DECLARE @grandparentId INTEGER; DECLARE @tableName VARCHAR( 255 ); -- If I am being inserted or deleted, and I am not a top level -- category, then my parent's pdf record needs to be set so that -- the pdf file is updated IF (SELECT id FROM Inserted) IS NOT NULL BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Inserted), @parentId = (SELECT parentCategory FROM Inserted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId); END ELSE BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Deleted), @parentId = (SELECT parentCategory FROM Deleted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId) END -- If I am not a top level category, set my parent's pdf to be -- updated IF @parentId <> -1 BEGIN SELECT @parentPdfId = (SELECT pdfManagerId FROM smvcModImageManagerCategory WHERE id = @parentId); UPDATE smvcModPdfManager SET data_last_updated = GETDATE() WHERE id = @parentPdfId; ENDGO

However, when I execute a statement like:

sql Code:

Original
- sql Code

DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))

DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))

I get an error because the virtual 'Deleted' table has more than one record in it. So, what I really need is advice on how to turn the above trigger into something that will be able to handle multiple deletes.

Thank you.

View Replies !

Trigger Not Firing On Cascade Delete Table
I have three tables:
BulkMemberHeader - which has a cascade delete on BulkMemberDetail of any related records
BulkMemberDetail €“ which has a DELETE trigger which gets the member ID from deleted and deletes the member record from the member table
Member

This issue:
> When I delete a record from BulkMemberDetail the trigger fires and deletes the record from the Member table as it should
> If I delete a record from the BulkMemberHeader, all corresponding records in BulkMemberDetail are deleted, but the trigger to delete the record in the Member table does not seem to fire

Is it a limitation on SQLServer 2000 that does not allow triggers to fire in a scenario like this?

Any suggestions or comments would be great.

Thanks,

Mike

View Replies !

Multiple Insert Into Multiple Tables With A Stored Procedure
Hello
I am building a survey application.
I have 8 questions.
Textbox - Call reference
Dropdownmenu - choose Support method
Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew

View Replies !

OnError Event Firing Multiple Times
I have an SSIS package that contains a For Each Loop Container. I have three Data Flow tasks within the container. I have an OnError event handler associated with the encapsulating container. When one of the Data Flow tasks within the For Each Loop Container fails, the OnError for the Loop Container gets called 5 times. The OnError handler is just a script task that sends a notification email. I am not explicitly Dts.taskresult = failure, nor am I calling FireError.

View Replies !

Importing Data In Datatable Using SSIS Package Trigger On Insert Is Not Firing On That Table
Hi
I am Importing data in datatable using SSIS package . I made trigger on that table on insert. The trigger on insert is not firing on that table
Please help
Thanks
CP

View Replies !

Trigger And Stored Procedure
What is trigger!what is differance beetween trigger and stored-Procedure!

View Replies !

Stored Procedure Or Trigger Or Both Or ???
HiThis problem involves 2 columns in my Product table - ReleaseDate(varchar) and ReleaseClass(varchar)When a new product is Entered, the current date is inserted into ReleaseDate & ReleaseClass = NewWhen product is a month old, I want ReleaseClass to = RecentWhen product is a month 3 mths old, I want ReleaseClass to = NormalWhen the product is a month old, is there a method to automatically run a Stored procedure say to change the ReleaseClass field??any code or links on how i go about this would be appreciated + i've never used a triggerCheers!!

View Replies !

Trigger Vs Stored Procedure
Hi,
Please help me to find this answer.
We know Trigger are a type of stored procedure,and can be activated whether by a insert ,update or delete event of a table.
We also know that stored procedure are quick due to their execution plan which are already stored in the memory once complied.
But what about triggers?
what is the mechanism of triggers?How they work? And how fast they are from Tsql queries?Is there any mechanism to calculate or measure the efficiency of triggers?
Please comment if anybody knows the answers.
Thanks!!
Joydeep

View Replies !

Get Return Value From Stored Procedure And Trigger
Hello there,
I searched for answers to the above topic, but could not find what I want. My stored procedures and triggers are returning a message based on the result, mostly error messages. How can I get that message using ASP.Net? Should I use an output parameter?
Thank you for your help.

View Replies !

How To Approach (Trigger-Stored Procedure )
Hi AllI need opinions on how to approach my task.I currently have 3 tables: the master table, the archive and a temptable.MASTER: has 3 fields ProductID and ProductNo and ReleasedARCHIVE: Has 3 ProductID, ProductNo, SoldDateTEMP: ProductID, ProductNo, SoldDateI have a trigger on the master table upon deletion to archive. This istriggered from a seperate routine from a vb app to delete a recordreal time.CREATE Trigger Archive_Proc On dbo.MASTERFor DeleteAsDeclare @iDate As DateTimeSet @iDate = GetDate()If @@RowCount = 0 Returnset Nocount onInsert Into ARCHIVE(ProductID, ProductNo, SoldDate)Select ProductID, ProductNo, @iDate from deletedMy problem is that I have a temp table that gets filled from aseperate transaction.It needs to be matched against the master tablethen deleted at both master and temp. but the issue is that the temptable contains its own SoldDate value that needs to be archived.Q 1: if I use a stored proc. how do i pass the SoldDate value to thetrigger as Triggers dont use GVs.Q 2: How do I set up the stored procedure to delete with multipletables. I can get it to UPDATE but not delete....CREATE PROCEDURE COMPARESOLD@Pool SmallintASSet NoCount onUpdate MASTERSet Released = 2From TEMP, MASTERWhere TEMP.ProductNo = MASTER.ProductNoAND TEMP.ProductID = MASTER.ProductIDAND INVENTORY.Released = 1hopefully someone can lead me to the right direction...Thanks

View Replies !

Trigger Executing A Stored Procedure
I am having a issue with executing a stored procedure from a trigger. I have two tables, a staging table, and a live table. The staging table has a FOR INSERT trigger on in that executes a stored procedure, and also uses some error handling. However, I have placed a primary key on the live table, and I'm inserting data into the table that will violate that primary key. This is to test that the transaction gets rollback correctly. However, the data never gets inserted into the staging table, nor will any raiserror kick off. The stored procedure gets called, and errors out with out calling my if @@ERROR <> 0 statement, or inserting data into my staging table. How can I still insert data into the staging table and call the @@ERROR statement? Can I check the constraints of the table called by the stored procedure before actually calling the stored procedure? I have also tried the INSTEAD OF INSERT as well, that doesn't work either. Thanks.

View Replies !

Calling Stored Procedure In Trigger
Hi

I have a problem calling stored procedure in trigger..

When no exception occures stored procedure returns the value but if any exception occures executing that stored procedure then stored procedure will not return any value..

I have handled exception by returning values in case if any..

Here is the stored procedure

CREATE PROCEDURE BidAllDestinations
(
@ITSPID int,
@DestinationID int,
@BidAmount decimal (18,4),
@BidTime datetime,
@intErrorCode int out
)

AS
DECLARE @GatewayID int
DECLARE @GatewayExist int
SET @GatewayID = 0
SET @GatewayExist = 0
SET @intErrorCode = 0

UPDATE BID FOR CORRESPONDING GATEWAY
DECLARE GatewayList CURSOR FOR

SELECT Gateways.GatewayID
FROM Gateways INNER JOIN
GatewayDestinations ON Gateways.GatewayID = GatewayDestinations.GatewayID INNER JOIN
ITSPs ON Gateways.ITSPID = ITSPs.ITSPID
Where Gateways.ITSPID = @ITSPID AND DestinationID = @DestinationID

OPEN GatewayList

FETCH NEXT FROM GatewayList INTO @GatewayID

IF (@GatewayID = 0)

SET @intErrorCode = 1
ELSE
BEGIN
-- CHECK @@FETCH_STATUS TO SEE IF THERE ARE ANY MORE ROWS TO FETCH
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT@GatewayExist = Gatewayid
FROMTerminationBids
WHEREGatewayid = @Gatewayid AND DestinationID = @DestinationID

IF @GatewayExist > 0

UPDATE TerminationBids
SET BidAmount = @BidAmount,
BidTime = getdate()

WHERE GatewayID = @Gatewayid AND DestinationID = @DestinationID

ELSE

INSERT INTO TerminationBids (GatewayID, DestinationID, BidAmount)
VALUES (@GatewayID,@DestinationID,@BidAmount)

IF @@ERROR <> 0
BEGIN
GOTO PROBLEM
CLOSE GatewayList
DEALLOCATE GatewayList
END

FETCH NEXT FROM GatewayList INTO @GatewayID

END
CLOSE GatewayList
DEALLOCATE GatewayList

END
PROBLEM:
BEGIN

SET @intErrorCode = 100

END
RETURN @intErrorCode
GO

TRIGGER CODE:::

CREATE TRIGGER TR_TerminationBid
ON dbo.TerminatorBidHistory FOR INSERT

AS

DECLARE @ITSPID int
DECLARE @DestinationID int
DECLARE @BidAmount decimal (18,4)
DECLARE @BidTime datetime
DECLARE @intErrorCode INT
DECLARE @DistinationList varchar (8000)
DECLARE @DestinationLevel varchar (100)
SET @intErrorCode = 0
SET @ITSPID = 0
SET @DistinationList = ''
-- CHECK ITPSID' S VALIDITY

SELECT@ITSPID = i.ITSPID, @DestinationID= i.DestinationID,
@BidAmount = i.BidAmount, @BidTime = i.BidTime
FROM Inserted i
INNER JOIN ITSPS ON ITSPS.ITSPID = i.ITSPID
INNER JOIN Destinations ON Destinations.DestinationID = i.DestinationID

EXEC BidAllDestinations @ITSPID,@DestinationID,@BidAmount,@BidTime, @intErrorCode = @intErrorCode output
SELECT @intErrorCode
Following should return value for @intErrorCode if any exception occures

Any one can help what is wrong with it?

Thanks

View Replies !

How Return A Value From Trigger To Stored Procedure?
I have a little problem. I have a trigger on a table TakesCourse that checks how many points a student currently is taking. If the current number + the incomming value is greater than 30 I want to return a value to the stored procedure that has the insert statment that fires the trigger. Then in the stored procedure I check this value and if its not ok, then I return -1 with the return parameter.

But I don't know how to send data back to the SP from the trigger.

I want to grab the error in the SP, I dont want to use, say a raiserror straight back to the client.

Any help appriciated.

/Magnus

Pasting in my current code:
CREATE TRIGGER TrigCheckPoints ON TakesCourse
FOR INSERT, UPDATE
AS

SET NOCOUNT ON

IF UPDATE(studentID)
BEGIN

DECLARE @studentIDinsINT
DECLARE @courseIDinsINT
DECLARE @pointsNowINT
DECLARE @pointsCourseINT

/* Get studentID and courseID being inserted */
SELECT @studentIDins=studentID, @courseIDins=courseID
FROM inserted

/* Get students total points now */
SELECT @pointsNow=SUM(C.points)
FROM TakesCourse TC
INNER JOIN Course C ON TC.courseID= C.CourseID
WHERE TC.studentID=@studentIDins

/* Replace NULL with 0 */
IF @pointsNow IS NULL
BEGIN
SELECT @pointsNow=0
END

/* Get points of course being inserted */
SELECT @pointsCourse=points
FROM Course
WHERE courseID=@courseIDins

IF (@pointsNow+@pointsCourse) > 30
BEGIN
/* Returning -1 means failed */
RETURN -1 --this needs to be replaced somehow
END
/* Returning 0 means OK */
Return 0 --this needs to be replaced somehow
END

Problem is with the return. You cant use values with return in a trigger. How do I send data back? with a temptable?

My SP code:
CREATE PROCEDURE TakesCourseInsert
(
@studentID int,
@courseOccasionID int,
@courseID int)
AS
BEGIN
SET NOCOUNT ON

BEGIN TRAN
/* SELECT NULL needed to be able to return the return parameter correctly */
SELECT NULL

INSERT INTO TakesCourse(studentID,
courseOccasionID,
courseID)
VALUES(@studentID,
@courseOccasionID,
@courseID)
--Grab trigger return value here somehow
IF (@triggererror!=0)
BEGIN
ROLLBACK TRAN
RETURN @triggererror
END

COMMIT TRAN
RETURN 0
END

View Replies !

Trigger Calling Stored Procedure???
can a trigger firing cause a stored procedure to execute!! if this can be done then I will have more questions to follow! thanks, Scott

View Replies !

Fire Trigger From Within A Stored Procedure Possible?
Is this possible?

View Replies !

Trigger Or Stored Procedure Question
I have a trigger that checks if a particular field in an application is being messed with. I am only allowing users to update this field when it is empty. So, I am not allowing them to change the value stored in the field (they will receive an email notification) because a backend operation is going on that could screw things up.

Aside from that, I want the application to refresh the page when the email goes out to go back and display the original contents of the field.

Now, I know this obviously cannot be done from a trigger since it is only dealing with the database. But, does someone have a quick and dirty way of accomplishing this?

Thanks

View Replies !

Trigger Starting Stored Procedure
CREATE TRIGGER check_availablerooms ON Reservation
AFTER INSERT
AS
DECLARE @Startdate varchar(20)
DECLARE @Enddate varchar(20)
SELECT @Startdate = ArrivalDate FROM INSERTED
SELECT @Enddate = DepatureDate FROM INSERTED
IF (@Startdate <> 'NULL' AND @EndDate <> 'NULL')
BEGIN
EXECUTE print_availablerooms @Startdate, @Enddate
END

Made this trigger so that all available rooms in the inserted period should show up on the screen. The trigger is working fine in Query Analyzer, but when i try to use it in Access it doesn't produce the result i want.

I want the trigger to run the stored procedure using the arrivaldate and departuredate entered in access as the input arguments.

Does anyone have a solution to this problem? I hope someone can post a code-example or a well described solution to this problem.

View Replies !

Auditing - Trigger Or Stored Procedure

Hi All,

We have a requirement in the project to do auditing for some of the tables. We have come across different approaches of implementing auditing.

The following are the approaches:

Create a generic stored procedure to do auditing. Call this procedure whenever any tables that require auditing have insert/update/delete operation performed on it. Auditing and the DML operation should be part of a single transaction.
Create a generic CLR trigger to do the auditing. The CLR trigger can be attached to the tables that require auditing.
Create separate audit tables and triggers for each table which require auditing.

I would like to know whether there are any disadvantages of using triggers in production server. Could anyone please help me in identifying the best approach for implementing auditing?

Will there be any situation when the DML statements execute and the corresponding trigger fails?

Is there any performance degradation on using triggers for auditing compared to including the auditing logic implemented in the stored procedures? We have been advised not to use triggers in production environment. But we are not clear about the reason for this.

PLEASE NOTE THAT I DO NOT WANT TO REPLACE STORED PROCEDURE WITH TRIGGERS TO IMPLEMENT BUSINESS LOGIC. BUT IN THE SCENARIO THAT I DISCUSSED, I NEED TO CAPTURE THE LOG INFORMATION WHENEVE A DML STATMENT IS EXECUTED AGAINST THE TABLES. WHAT IS THE PREFERRED APPROACH HERE...USING TRIGGERS OR STORED PROCEDURES?

In general, is there any disadvantage in using triggers for auditing? Has anyone faced any issues with triggers? (Triggers not invoked during DML operations or any performance related issues) Please let us know.

Thanks,

View Replies !

Trigger Or Stored Procedure Question
Persons Table
PersonID                int            NOT NULL PRIMARY KEY
PersonFatherID       int            NULL         FOREIGN KEY Persons(PersonID)
PersonMotherID      int            NULL         FOREIGN KEY Persons(PersonID)
PersonGeneration   int            NULL
PersonFirstName    nchar(20) NOT NULL
PersonLastName    nchar(20) NOT NULL

Spouses Table
SpouseID               int            NOT NULL PRIMARY KEY
HusbandID             int            NOT NULL FOREIGN KEY Persons(PersonID)
WifeID                  int            NOT NULL FOREIGN KEY Persons(PersonID)

Persons Table Data
PersonID PersonFatherID   PersonMotherID PersonGeneration PersonFirstName PersonLastName
1             1                        1                       1                           Adam                   Smith
2            2                        2                       1                           Evelyn                  Smith
3             1                        2                       2                           Caleb                   Smith
4             NULL                 NULL                 0                           Sara                    Jones

Spouses Table Data
SpouseID HusbandID        WifeID
1             1                       2
2             3                       4

I want to update PersonGeneration column in Persons table when that person is added into Spouses table. For example, if Sara Jones is added as wife to Caleb Smith in Spouses table, then it should update Sara's PersonGeneration column (0) with Caleb's PersonGeneration (2) in Persons table. So, the rule is if PersonFatherID=NULL and PersonMotherID=NULL and PersonGeneration=0, then update this person's PersonGeneration with his/her spouse's PersonGeneration. I am thinking about a trigger or a stored procedure or both. Any help would be greately appreciated.

Thanks,
Mohan John

View Replies !

Execute A Trigger From Stored Procedure

Hi,

Is it possible to run trigger from a stored procedure?

View Replies !

Calling A Stored Procedure In A Trigger
Hello,

I am trying to test a simple trigger on insert and it does not work when I call EXEC sp_send_cdosysmail.
However, the stored procedures does work if I right-click on it and select Execute Stored Procedure.

Below is a simple version of the trigger I am trying to implement. I know it works in SQL Server 2000 and 2005 but can't seem to get it to work in SQL Server 2005 Express. Any help is greatly appreciated!

ALTER TRIGGER [dbo].[trig_Tableinsert]
ON [dbo].[Table]
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

Print 'Hello'

-- Insert statements for trigger here
EXEC sp_send_cdosysmail some@one.com', 'notify@me.com','New Insert', 'test'

END

Thanks!

View Replies !

Ytd Expenses---Trigger Or Stored Procedure
I'm trying to come up with a Stored Procedure or a Trigger to Sum up monthly =Expenses to the YTDExpenses Column. Can Someone help please.

I have a Monthlyexpense column. How do I Sum up this column and put the Total in my ytdexpenses column. Do I use a stored procedure, because I want the monthlyExpenses to SUm up every time I submit a monthly expense to the database and siplay in the ytdExpenses Column.
When I Write a Query all of the rows in the ytdExpenses shows the same amount and do not total up every time I submit to the database. Help please.

monthlyExpenses ytdExpenses
$1,000 $1,000
$2,000 $3,000
$3,000 $6,000
$2,000 $8,000
$5,000 $13,000

View Replies !

Multiple Stored Procedure...or 1 Dynamic Procedure?
Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...

my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...

if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...

this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...

but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??

Cheers,
Justin

View Replies !

Using Trigger/Stored Procedure (Delete, Insert)?
I have 3 tables...TableA, TableB, TableC TableA - Personal InformationPersonalInfoId (Primary) , First Name,Last NameTableB - Personal Information To Department IDReferenceID, FKPersonalInfoId, FKDepartmentIdTableC - DepartmentDepartmentId, DepartmentNameI am coding Asp.Net VB using VWD express with Sql Server Express. I know how to create a stored procedure to delete, insert and even update a record in TableA, TableB, TableC respectively.If I need to delete a record in TableC, which has a related record in TableB, I have read that I need to use a Trigger. I never have used a Trigger and it is new to me. Can someone point me a way on how to use one in this case of my deleting scenario. Pretty much, if a user clicks on a delete button, and deletes a record in my TableC, I dont want a FKDpartmentId in my TableB that doesnt exist anymore because it was deleted in TableC or prevent a user from deleting that record till the relationship in TableB is no longer valid. In the same vain, If I have a input form which ask the user to enter their First Name and Last Name and Department, i would like to add those records in TableA for First and Last Name, TableB for the Department. Once again, how do I create a Trigger that if I insert a record in Table A to also insert the information for Department in Table B, if its successful in my stored procedure. Hope that made sense.Thanks.

View Replies !