April 7, 2010 at 2:50 am
Hi All...
I used to work with SQL a LONG time ago (R:Base running on DOS!).
With a lot of help from internet, books, etc some of it is comming back quickly, other stuff takes a lot longer 😀
Going at this level in MSSQL is somewhat new to me and I have the following situation:
I have (SQL2000-SP4) two tables; [Agent] and [Asset]. On [Agent] I created a trigger.
When in [Agent] a new record is inserted with [Agent].[Name] = 'Agent', the trigger has to start a Stored Procedure: sp_InsertAsset parsing parameter [Agent].[_Guid]
The Stored Procedure than should take the parameter to create a new record in [Asset] with [Asset].[_Guid] is set to the parsed parameter. Also a small calculation is done and the result is entered in another column of [Asset].
When I manually do an Insert, the Trigger is kicked off, the SP runs and all is fine: A new record in [Asset] is created where [Asset].[_Guid] is equal to [Agent].[_Guid] and the correct calculated result is stored in another column.
Now when I let the proces run (Install an Agent that does insert a record in [Agent], nothing seems to happen!
So I have two questions:
- how to debug this?
I tried PRINT but that only works when running in Query Analyzer which is manually and that is running fine. I would like to have some sort of PRINT command in my scripts that does some "status" printing into a txt file so I can see which value is used etc, etc.
- why is it running manually and not in "auto-mode"? What could be the case here?
All your "two-cents" are welcome..
Thx in advance
Guido
EDIT:
The two scripts:
CREATE TRIGGER [NewAgentRegistration]
ON [dbo].[Client_Agent]
AFTER INSERT
AS
DECLARE @RGuid nvarchar(38)
BEGIN
IF EXISTS
(
SELECT 'True'
FROM Inserted i
WHERE i.[Name] = 'Agent'
)
SELECT @RGuid = (SELECT i.[_RGuid] FROM inserted i)
END
BEGIN
-- PRINT 'Starting sp_CreateASNumber with ' + @RGuid
EXECUTE dbo.sp_CreateASNumber @RGuid
END
CREATE PROCEDURE sp_CreateASNumber
@DIUG nvarchar(38)
AS
BEGIN
-- PRINT @DIUG
SET @DIUG = CAST(@DIUG as uniqueidentifier)
-- First check if GUID already exists.
-- If not, first create Record and add GUID and default values
IF NOT EXISTS (
SELECT [_RGuid]
FROM [dbo].[Asset] anid
WHERE [anid].[_RGuid] = @DIUG
)
BEGIN
INSERT INTO [dbo].[Asset]([_RGuid], ASNumber, ASName, ASRDate, ASEDate)
VALUES (@DIUG, '', '', '', '')
END
BEGIN
-- Adjust ASNumber value using the [_Id] value
UPDATE [dbo].[Asset]
-- Take the length of [_Id]. Add leading zero's upto a total of 5 positions.
-- Add [_Id] after the zero's and put the letter A in front
SET ASNumber = N'A' + REPLICATE('0',5-(LEN([_id]))) + CONVERT(varchar(5), [_id], 5)
WHERE [Asset].[_RGuid] = @DIUG
END
END
GO
------- End sp -----
April 7, 2010 at 5:19 am
You have two problems:
1. Your trigger will not cope if multiple rows are inserted.
2. You have a multi-statement trigger with no error handling.
Also, ASNumber is just a formated copy of [_id] so you could get rid of it in the DB.
If you really want it in the DB, have it as a calculated column.
ALTER TABLE dbo.Asset
DROP COLUMN ASNumber
ALTER TABLE dbo.Asset
ADD ASNumber AS ('A' + REPLACE(STR([_id], 5), ' ', '0'))
This means your trigger reduces to:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER NewAgentRegistration
ON dbo.Client_Agent
AFTER INSERT
AS
SET NOCOUNT ON
INSERT INTO dbo.Asset(_RGuid, ASName, ASRDate, ASEDate)
SELECT _RGuid
,''
,'' -- Should this be a date???
,'' -- Should this be a date???
FROM inserted I
WHERE [Name] = 'Agent'
AND NOT EXISTS
(
SELECT *
FROM dbo.Asset A
WHERE A._RGuid = I._RGuid
)
GO
There is also the possibility that your application is using bulk inserts, in which case the trigger will not be run.
K.I.S.S.
April 7, 2010 at 2:39 pm
I agree with Ken on all his points. Your mistakes are common ones when dealing with triggers in SQL Server. Ideally, IMO, all of this should be handled by the application or the stored procedure called by the application instead of in a trigger, this is business logic that should be in an obvious place and triggers are never obvious.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 8, 2010 at 7:15 am
Hi Ken and Jack.
First, thx for your time & input.
Now, I hear what you both are teaching me. But I can not touch the apps-logic due to update-policies from the vendor not can I change the database like Ken said with a caclulated colum. I can add a Trigger. So, I have to deal with the options SQL is giving me. My thought was have a Trigger for starters and let a SP do the work.
At this point I also get confused as what I did was only using a Trigger to kick-off the SP. The "BL" was basicly done by the SP.
Ken's example is just the opposite... I do not want to put a good marriage into flames, but as far as I understand this now there is a conflict here.
So, I will try to put this information together in creating a Trigger executing an SP using the code Ken provided...
That should do the trick?
Guido
April 8, 2010 at 8:19 am
The issue with putting the logic in a stored procedure that is called from within a trigger is that you then need to loop through the inserted table and call the stored procedure for each row that meets the criteria. The solution Ken has proposed handles that situation without a loop so it will scale much better.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 8, 2010 at 8:34 am
Not adding anything here, just confirming that you are using SQL Server 2000 SP 4 as indicated in your original post.
April 8, 2010 at 9:21 am
Try something like:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER NewAgentRegistration
ON dbo.Client_Agent
AFTER INSERT
AS
SET NOCOUNT ON;
BEGIN TRY
INSERT INTO dbo.Asset(_RGuid, AsNumber, ASName, ASRDate, ASEDate)
SELECT _RGuid
,''
,''
,'' -- Should this be a date???
,'' -- Should this be a date???
FROM inserted I
WHERE [Name] = 'Agent'
AND NOT EXISTS
(
SELECT *
FROM dbo.Asset A
WHERE A._RGuid = I._RGuid
);
MERGE dbo.Asset AS A
USING inserted I
ON A._RGuid = I._RGuid
WHEN MATCHED THEN
UPDATE SET ASNumber = 'A' + REPLACE(STR([_id], 5), ' ', '0');
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
ROLLBACK;
RAISERROR(@ErrorMessage, 16, 1);
END CATCH
GO
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply