Trigger function help - 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

General SQL Server Forums

New to SQL Server Programming

Trigger function help

Author

Topic

mistylove98
Starting Member

5 Posts

Posted - 2015-05-05 : 13:56:04

I am trying to create a trigger function that when either an update or insert is done country will always be in upper case can anyone tell me why there is a red line under my table name saying it doesnt exist when i know it does im looking right at it on the side view

CREATE TRIGGER country_Insert_Update 
   ON  dbo.cis111_LMccrorey_Customers
   AFTER INSERT,UPDATE

AS 

Update Customers
   set Country = UPPER (Country)
   where Country in (Select Country from inserted)


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

  



END
GO

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-05-05 : 15:04:27

Do you have a table named cis111_LMccrorey_Customers in your database? If you don't have that, that would explain the error. But, assuming that you do have that table, the trigger is fired when you insert or update the data in that table. When you do that, don't you want to be updating that table, rather than the Customers table? Also, the flow of the code is not correct. Try something like this:

CREATE TRIGGER country_Insert_Update 
   ON  dbo.cis111_LMccrorey_Customers
   AFTER INSERT,UPDATE

AS 
BEGIN
	SET NOCOUNT ON;

	UPDATE  c 
	SET     Country = UPPER(Country)
	FROM	dbo.cis111_LMccrorey_Customers c
			INNER JOIN INSERTED i ON
				i.WhateverIsYourPrimaryKeyOfTheTable = c.WhateverIsYourPrimaryKeyOfTheTable

END
GO

Even as I am typing this, I am thinking that it doesn't quite look right. It looks like you have a country table, and what you probably need is a trigger on that table.

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 04:43:35

I would add:


WHERE Country <> UPPER(Country)  COLLATE Latin1_General_BIN2

so that only non-uppercase Country data is "fixed"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources