How to call a stored proc. from trigger? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

How to call a stored proc. from trigger?

Author

Topic

stefy
Starting Member

2 Posts

Posted - 2002-08-23 : 10:59:57

I have a SP with parameters which do some checks etc. and works fine.
But I need to call it whenever an insert/update occurs on a particular table, so I did it this way:
-----------------------------------
CREATE TRIGGER [cashfire1] ON [FINTRADE]
FOR INSERT
AS
declare @tdate varchar(225)
declare @braid int
declare @comid int
declare @usrid int
declare @ftrid int
declare @source int
SELECT @usrid=n.APPROVALUSER,
@tdate=convert(varchar(10),n.APPROVALDATE,103),
@braid=n.BRAID,
@comid=n.COMID,
@ftrid=n.ID,
@source=n.SOURCE
FROM inserted n

EXEC MyCash @comid, @braid, @ftrid, @usrid, @tdate
___________________________________
thus giving the SP parameters and fire it.
It doesn't work. What's wrong?
Thanks in advance.

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-08-23 : 11:20:59

Could you include the MYCash procedure?

It's possible the trigger is firing, and the results of the MYCASH procedure are simply being ignored.

It's also possible the MYCASH procedure provokes another insert or update to the same record, which may require 'recursive trigger' or 'cascading trigger' options to be properly configured on your server.

Seeing the MYCASH proc would help determine this.

stefy
Starting Member

2 Posts

Posted - 2002-08-23 : 11:27:30

Here is the procedure:
_____________________________________
ALTER PROCEDURE MyCash
(@Pcomid int ,
@Pbraid int ,
@Pftrid int,
@Pusrid int ,
@Pdate varchar(30)
)
AS
BEGIN
DECLARE @ROWSAFFECTED INT, @ddate varchar(225), @tdate varchar(225), @ndate varchar(225),
@rttid int, @cshid int, @braid int, @comid int, @usrid int, @rctid int

SET @ROWSAFFECTED = 0
SET @ddate=CONVERT(VARCHAR(10),GETDATE(),103)+' '+convert(varchar(10),GETDATE(),114)
SET @ndate=CONVERT(VARCHAR(10),GETDATE()+1,103)+' '+convert(varchar(10),GETDATE()+1,114)

select @cshid=CSHID from RETAILTRADE where ftrid = @Pftrid

--Check if we have the cash closed already for today
SELECT @usrid=USRID,
@tdate=convert(varchar(10),DETAILEDDATE,105)+' '+convert(varchar(11),DETAILEDDATE,114),
@cshid=CSHID, @braid=BRAID, @comid=COMID, @rttid=RTTID
FROM RCASHTRANS
WHERE COMID=@Pcomid AND CSHID=@cshid AND BRAID=@Pbraid AND
cast(DETAILEDDATE as varchar(30))>=@ddate AND
cast(DETAILEDDATE as varchar(30))<@ndate AND
RTTID=3 AND FTRID=0 AND
DETAILEDDATE = (SELECT MAX(DETAILEDDATE) FROM RCASHTRANS WHERE COMID=@Pcomid AND
CSHID=@cshid AND BRAID=@Pbraid AND
cast(DETAILEDDATE as varchar(30))>=@ddate AND cast(DETAILEDDATE as varchar(30))<@ndate AND RTTID=3 AND FTRID=0)

SET @ROWSAFFECTED = @@ROWCOUNT
if @ROWSAFFECTED>0
begin
if @usrid=@Pusrid
begin
RAISERROR (50001, 10, 1, @Pdate)
--print 'The cash is already closed for today'
end
end

--Check if we have left opened cash in previous working day prikljuchvane na kasa
SET @ROWSAFFECTED = 0
DECLARE CCASH CURSOR
FOR
SELECT ID, USRID,CSHID, BRAID, COMID, RTTID, convert(varchar(10), DETAILEDDATE,103)
FROM RCASHTRANS
WHERE COMID=@Pcomid AND CSHID=@cshid AND BRAID=@Pbraid AND USRID=@Pusrid AND
FTRID=0 and rttid in(2,3)
order by detaileddate desc

OPEN CCASH
DECLARE @PrevWorkDate varchar(30),@PDate1 varchar(10),
@Crctid int, @Cusrid int, @Ccshid int, @Cbraid int, @Ccomid int,
@Crttid int

set @PDate1 = convert(varchar(10),@PDate)

FETCH NEXT FROM CCASH INTO @Crctid,@Cusrid,@Ccshid,@Cbraid,@Ccomid,@Crttid,@PrevWorkDate

select @PDate1 as paramdate, @PrevWorkDate as prevdate, @@FETCH_STATUS as fetchstatuss
select @Crctid,@Cusrid,@Ccshid,@Cbraid,@Ccomid,@Crttid

WHILE (@@FETCH_STATUS = 0)
BEGIN
if (@PrevWorkDate<>@Pdate1)
begin
if (@Crttid = 3)

BREAK

else
begin
-- DELETE FROM RCASHTRANS WHERE ID=@rctid
--We have such case: opened cash for previous day
RAISERROR (50002, 10, 1, @PrevWorkDate)
BREAK
end
end

FETCH NEXT FROM CCASH INTO @Crctid,@Cusrid,@Ccshid,@Cbraid,@Ccomid,@Crttid,@PrevWorkDate
select @PDate1 as paramdate, @PrevWorkDate as prevdate, @@FETCH_STATUS as fetchstatuss
select @Crctid,@Cusrid,@Ccshid,@Cbraid,@Ccomid,@Crttid
END
CLOSE CCASH
DEALLOCATE CCASH
END
________________________________________________

Edited by - stefy on 08/23/2002 11:31:53

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-08-23 : 12:22:59

By default, It looks like you have an AFTER trigger.

This means that the record is inserted into the table before the trigger is processed. It may raise an error, but unless your trigger specifically states to REMOVE the record when the error occurs, the trigger cannot perform any corrective action.

You could...

use an INSTEAD OF trigger,
--Check @@error for a value =0 --won't work if error is in another
--sproc
--return a status code from the stored procedure
if valid then Insert into table "Select * from inserted"

However, another trigger form might work better for you.

Anybody out there have a good example of a trigger that skips the insert if a condition is not met?

Edited by - kevin snow on 08/23/2002 12:26:15

Edited by - kevin snow on 08/23/2002 12:35:48

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources