another TRIGGER Question - 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 2000 Forums

Transact-SQL (2000)

another TRIGGER Question

Author

Topic

gingerpits
Starting Member

7 Posts

Posted - 2005-09-30 : 11:02:26

man, I'm lost on triggers. I need a good article/book to read. Here's my new problem:

I need to generate the generic code again for from varchar fields when a new record gets inserted into the table. Here is what I have so far:
IF (SELECT COUNT(*) FROM inserted = 1)
BEGIN
UPDATE tblMYTABLE
SET tblMYTABLE.txtCode = LEFT(dbo.RemoveNonAlphas(inserted.txtName),4) + LEFT(dbo.RemoveNonAlphas(inserted.txtCity),4) + inserted.txtST
FROM tblMYTABLE, inserted
WHERE inserted.nmbID = tblMYTABLE.nmbID
END

any help would be appreciated. thanks in advance.

Travis

Kristen
Test

22859 Posts

Posted - 2005-09-30 : 11:19:03

What's wrong with that then?

It will only "fire" when you UPDATE/INSERT exactly one row (which seems like a bad idea to me! but I don't know what you are trying to achieve)

Did I complain about the JOIN style already? If not let me know and I'll do that in my next reply

Kristen

gingerpits
Starting Member

7 Posts

Posted - 2005-09-30 : 12:12:58

yeah, you did. I'm trying to keep conventions that were set before I started to work here. You would like my sprocs more:)

I'm going to do everything on the front end instead of having the trigger do it, the way I originally wanted to. so thanks anyways, sorry to waste your time.

Kristen
Test

22859 Posts

Posted - 2005-09-30 : 13:45:03

No problem.

If you are going down the "everything at the front end" route I suggest you consider using Stored Procedures. You could kinda think of them as "front end", but the joy is you can:

a) test them standalone
b) patching them without rolling out a complete client application (and revert to an earlier version easily enough if you have to)

Personally I couldn't cope with

strSQL = "SELECT * FROM MyTable WHERE "
strSQL = strSQL & "Col1 LIKE '%" & REPLACE(strParameter, "'", "''") & "%'"

any more, it so unreadable, and finding the place where the code is when there is a bug AND trying to see what the concatenated string is - its just a nightmare. I now much prefer to do:

strSQL = "EXEC MySproc @PARAM1 = '" & REPLACE(strParameter, "'", "''") & "'"

instead and then have a SQL Source Code File for:


CREATE PROCEDURE MySproc
    @PARAM1 varchar(100)
AS
BEGIN
    SELECT * 
    FROM MyTable 
    WHERE Col1 LIKE '%' + @PARAM1 + '%'
END

that I can modify, test stand alone, and roll-out to Dev, QA, Production as I see fit.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources