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.
Go to Top of Page

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"
Go to Top of Page
   

- Advertisement -