Don't know what Trigger does - 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)

Don't know what Trigger does

Author

Topic

w1fini
Starting Member

4 Posts

Posted - 2009-11-01 : 19:48:19

Hello,

I am new to SQL Server 2005 and all its functionality like triggers.
I have a Access 2007 Database and I decided to put a SQL Server 2005 Express as a backend to manage the data. To reduce the network traffic I want to put as much application logic as possible from ACCESS to SQL Server via Triggers and stored Procedures. The problem is that I can find a possibility to debug the code I have written so the results are not always very clear to me. So I hope you can help me.

In my special scenario I have the tables customer and office. A customer belongs to the office where the contact was created. Depending on the officeNr I would like to generate a unique CustomerNr as a combination of the office initials and an inremental of the last customer of this office(like Perth 1, Perth 2, Sydney 1 etc.). The will be a CustomerID used as primary key but hidden to the normal user for joining information with other tables. So I can use the after insert trigger to change the information.

Here is the code I am intending to use:

CREATE TRIGGER dbo.trUpdateCustomerNr
   ON  dbo.Customer 
   AFTER INSERT
AS 

DECLARE @OfficeID int
DECLARE @maxCustomerNr nvarchar(255)
DECLARE @OfficeInitial nvarchar(10)

Declare @LengthOffice int
Declare @LengthCustomer int
Declare @NewNr int
Declare @CustomerNr nvarchar(255)

SET @OfficeID = (SELECT CreatedByOfficeCodeID FROM inserted)
SET @OfficeInitial = (SELECT o.OfficeInitial FROM dbo.Office as o inner join inserted i ON o.OfficeCodeID = i.CreatedByOfficeCodeID)
SET @maxCustomerNr = (SELECT c.CustomerNr FROM dbo.Customer c, (SELECT Max(dbo.Customer.CustomerID) AS maxID FROM dbo.Customer WHERE (dbo.Customer.CreatedByOfficeCodeID = @OfficeID)) maxcustId WHERE c.CustomerID = maxcustId.maxID)

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	set @LengthOffice = LEN(@OfficeInitial) + 1 
	set @LengthCustomer = LEN(@maxCustomerNr) - @LengthOffice
	set @NewNr = CAST (SUBSTRING( @maxCustomerNr, @LengthOffice, @LengthCustomer )AS INT) + 1
	set @CustomerNr = @OfficeInitial + ' ' + @NewNr

	update dbo.Customer
	set dbo.Customer.CustomerNr = @CustomerNr
	FROM dbo.Customer c inner join inserted i ON c.CustomerID=i.CustomerID
	where c.CustomerID = i.CustomerID

END

GO

I get the error message that I can not convert the Nchar value "Perth " into an integer. I dont know i have have the substring wrong but i intent to cut the String "Perth 1" into "Perth " and "1" and then increment the numberstring and put them together.
Is there any better way to do this or any way to debug sqlserver 2005 trigger to see what my code is doing?

Thank you very much in advance for your time and consideration.
Nic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-01 : 22:12:01

To reduce the network traffic, why don't you use stored procedures? Triggers are not typically recommended for performance reasons as it keeps the transaction open longer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

w1fini
Starting Member

4 Posts

Posted - 2009-11-01 : 23:54:34

Hi Tara,

Stored Procedure sounds good to me as well but this puts me in front of 2 new questions:
1.How do I start a stored Procedure in SQL Server 2005 from within Access 2007?
2.Can I debug what the stored Procedure does?

Pradip
Starting Member

32 Posts

Posted - 2009-11-02 : 01:38:54

I think your problem is only
cut the String "Perth 1" into "Perth " and "1" and then increment the numberstring and put them together.
If you use select @var+convert(nchar,convert(int,right(@var,2))+1)

will solve your error of conversation . and will increment value by one
you can decide whether to use it in loop as per your requirement.

Hope this may help you.

pradipjain

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-11-02 : 10:21:49

I agree with Pradip that your problem is in the conversion.
Changing

set @CustomerNr = @OfficeInitial + ' ' + @NewNr

set @CustomerNr = @OfficeInitial + ' ' + convert(varchar(10), @NewNr)

will likely solve your immediate problem.
The more serious issue is that the way you have written your trigger it will only work with single record transactions. I disagree with Tara here that this logic is better suited for a stored procedure, but your triggers MUST be written in set-based logic that supports multi-record transactions.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

w1fini
Starting Member

4 Posts

Posted - 2009-11-03 : 19:49:12

Hi

thank you for your advices. I still took me some time to figure out what the problem was. I finally downloaded the 90 days trial of Visual Studio Team Edition. With the debug functionality I could finally see the values of all the variables. Its a great feeling seeing into the black box...

I will describe the error here in the hope to prevent others from stepping into it:

1.Converting to nchar only gave 1 digit of the numbers. As long as there are only 9 Customers this funtion works fine, thus I changed this to nvarchar

2.The trigger runs after insert. It is supossed to get the max of the CustomerNr to fetch the last Customer for calculating. Actually it was getting the inserted row where this value was still blank. Thats like a cat chasing its own tail. So this got changed to Instead of

3.Last but not least the Insert needed to be adapted to:



    SELECT * INTO #Inserted FROM Inserted
    UPDATE #Inserted SET CustomerNr = @CustomerNr
      
    INSERT INTO dbo.Customer (@ListOfColumns)
    SELECT @ListOfColumns FROM #Inserted

The important part was to use @ListOfColumns with all the columns but the identity column. This help to prevent SQL Error #8101 (explicit value for identity column ...)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources