January 3, 2009 at 9:05 pm
Hi,
This is my first post and I need some little help here. I'm a newbie in SQL triggers.
In this problem I have 2 tables one named sectors and the second named sites.
Each site belongs to a sector. The relationship is done with the sites.pkSectorID and the sectors.fkSectorID.
Each sector has a unique code.
Each site has an ID (siteID) which is entered by the user. If the user doesn't provide a siteID a default value is created by concatenating the sector unique code with the sites.pkSiteID.
Each site ID must be unique.
I created the following trigger but all I get is null values. What am I doing wrong?
ALTER TRIGGER tg_new_siteID
ON dbo.sites
AFTER INSERT
AS
if (select count(*) from inserted where siteID = '') = 1
begin
UPDATE sites
SET sites.siteID = (SELECT sectors.sectorCode FROM sectors WHERE sectors.pkSectorID = (SELECT fkSectorID FROM inserted)) + (SELECT fkSectorID FROM inserted)
WHERE sites.pkSiteID = (SELECT fkSectorID FROM inserted)
end
Thanx in advance
January 4, 2009 at 2:48 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2009 at 11:48 am
CREATE TABLE sectors
(
pkSectorID INT IDENTITY(1,1) PRIMARY KEY,
sectorName NVARCHAR(100) NOT NULL,
sectorCode NVARCHAR(5) NOT NULL,
...
)
CREATE TABLE sites
(
pkSiteID INT IDENTITY(1,1) PRIMARY KEY,
siteID NVARCHAR(15), -- This must be a concatenation with the sectorCode and the pkSiteID.
fkSectorID INT,
...
)
-- Sector data examples
1, Toronto, TOR
2, Ottawa, OTT
3, Montreal, MTL
...
-- Site data examples
1, TOR1, 1
2, TOR2, 1
3, OTT3, 2
...
The user can provide a site id, but if this one is null, the default value would be the sectorCode + pkSiteID. How can I trigger this?
January 4, 2009 at 12:07 pm
Untested, but I think something like this should work.
IF EXISTS (SELECT 1 FROM inserted WHERE siteID IS NULL)
UPDATE Sites SET siteID = sectorCode + CAST(pkSiteID AS NVARCHAR(5))
FROM
Sites INNER JOIN Sectors ON Sites.fkSectorID = Sectors.pkSectorID
WHERE pkSiteID IN (SELECT pkSiteID FROM inserted)
Put that as the contents of the trigger.
I'm guessing the problem was because a field not passed defaults to null, not ''. Unless, of course, that's changed somewhere in the procedure that inserts into the site table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2009 at 12:30 pm
Cool it works like a charm!
Now let's take that a step further. Let say I would like to generate the site with the last number from the same sector.
Examples :
-- Sector data examples
1, Toronto, TOR
2, Ottawa, OTT
3, Montreal, MTL
...
-- Site data examples
1, TOR1, 1
2, TOR2, 1
3, OTT1, 2
4, OTT2, 2
5, TOR3, 1
6, MTL1, 3,
...
I guess I should write a stored procedure which takes the pkSectorID as a parameter and returns the last number of the generated site. I know how to do programming in C#, but not in SQL. What it would look like?
January 4, 2009 at 12:47 pm
nbourre (1/4/2009)
Cool it works like a charm!Now let's take that a step further. Let say I would like to generate the site with the last number from the same sector.
I guess I should write a stored procedure which takes the pkSectorID as a parameter and returns the last number of the generated site. I know how to do programming in C#, but not in SQL. What it would look like?
Not sure, because I don't fully understand.
For the examples data you gave, what results do you want for various parameters?
SectorID = 1, result = 5
SectorID = 2, result = 4
SectorID = 3, result = 6
Is that correct?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2009 at 12:50 pm
Can someone tell me what's wrong with this syntax? It tells that there's a syntax error near SELECT...
CREATE PROCEDURE dbo.getLastSiteNumber
(
@sectorID int = 5,
@lastSite nvarchar(15),
@lastNumber int
)
AS
/* SET NOCOUNT ON */
SET @lastSite = SELECT MAX(siteID)
FROM sites
WHERE fkSectorID = @sectorID
ORDER BY siteID DESC;
SET @lastNumber = SUBSTRING(@lastSite, 3, 12);
RETURN @lastNumber;
January 4, 2009 at 12:57 pm
Instead of using the pkSiteID as the number to concatenate the siteID I would like to increment the number from the last site from the same sector.
Example :
-- Site data examples
pkSiteID, siteID, fkSectorID
1, TOR1, 1 -- First site for Toronto
2, TOR2, 1 -- Second site for Toronto
3, OTT1, 2 -- First site for Ottawa
4, OTT2, 2 -- Second site for Ottawa
5, TOR3, 1 -- Third site number from Toronto was 2
6, MTL1, 3 -- First site for Montreal
...
January 4, 2009 at 1:16 pm
nbourre (1/4/2009)
Can someone tell me what's wrong with this syntax?
SELECT @lastSite = MAX(siteID)
FROM sites
WHERE fkSectorID = @sectorID
ORDER BY siteID DESC;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2009 at 1:26 pm
Hi Gail,
I can't compile, I get following error :
Column "sites.siteID" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Here's the code :
CREATE FUNCTION dbo.GetLastSiteNumber
(
@sectorID int
)
RETURNS nvarchar(15) -- For testing purpose, next step is to retrieve the number.
AS
BEGIN
DECLARE @lastSite nvarchar(15)
SELECT @lastSite = MAX (siteID)
FROM sites
WHERE (fkSectorId = @sectorID)
ORDER BY SiteId DESC
RETURN @lastSite
END
January 4, 2009 at 1:42 pm
Hello,
Try removing the Order By Clause. (I can't really see the purpose of having it in).
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
January 4, 2009 at 1:51 pm
As the error says, siteID cannot be in the order by clause, because it's not contained in the group by and it's not an aggregate.
What's the point of having it there?
One thing to be careful of. This procedure is written in such a way that, if it's used in a trigger, the trigger will give unexpected results if more than one row is inserted at a time.
It's not usually a good idea to call procedures from triggers because of this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2009 at 1:54 pm
Cool! I made everything work.
The function :
ALTER FUNCTION dbo.GetLastSiteNumber
(
@sectorID int
)
RETURNS int
AS
BEGIN
DECLARE @lastSite nvarchar(15)
DECLARE @lastNumber int
SELECT @lastSite = MAX (siteID)
FROM sites
WHERE (fkSectorId = @sectorID)
IF (@lastSite IS NOT NULL)
SET @lastNumber = CONVERT(int, SUBSTRING (@lastSite, 4, 12)) + 1
ELSE
SET @lastNumber = 1
RETURN @lastNumber
END
The trigger :
ALTER TRIGGER tg_siteInsert
ON dbo.sites
FOR INSERT
AS
BEGIN
IF EXISTS (SELECT 1 FROM inserted WHERE siteID IS NULL)
UPDATE sites SET siteID = sectorCode + CONVERT(nvarchar(5), dbo.getLastSiteNumber(sites.fkSectorID)) --CAST(pkSiteID AS NVARCHAR(5))
FROM sites INNER JOIN sectors ON sites.fkSectorID = sectors.pkSectorID
WHERE pkSiteID IN (SELECT pkSiteID FROM inserted)
END;
If someone sees something I might improve please let me know!
Thanks Gail!
January 4, 2009 at 2:13 pm
D'oh! There's a bug with the MAX.
-- Site data examples
pkSiteID, siteID, fkSectorID
...
11, TOR8, 1
12, TOR9, 1
13, TOR10, 1
14, TOR10, 1 -- The max doesn't sort the data with the number
15, TOR10, 1
I will need to ORDER BY the site number without the sectorCode and then get the first value.
How can I workaround this problem?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply