sql trigger question

[k2]

so i'm in the middle of making an automated notice system that's handled via triggers and functions in my db (sqlserver). i have to stop working on it for today; but i was wondering if this is going to work or if i need to change it.

the part that i'm not sure about is the msg insert; the user_to field will usually contain more than 1 result from the nested select. is that legal? i can't really tell without testing because i've done things like this before but not exactly.

(@Rent will be defined later because i just haven't gotten that far in the variable declaration/logic)

CREATE TRIGGER [Property_Listings_Insert] ON dbo.Property_Listings
FOR INSERT
AS

DECLARE @Total int, @UnixTime int, @ID int, @City int, @Type int, @Body nvarchar(2000)

SET @Total = (SELECT COUNT(@@IDENTITY) FROM inserted)

IF @Total = 1
BEGIN
/* Setting variables to be used for ecommerce and automatic user notification */
SET @UnixTime = (SELECT DateDiff(s, '1970/1/1 0:00:00', DateAdd(hh, -8, (SELECT [Timestamp] FROM inserted))))
SET @ID = (SELECT @@IDENTITY FROM inserted)
SET @City = (SELECT City FROM inserted)
SET @Type = (SELECT Type FROM inserted)
SET @Body = ('You may be interested in the following ad: http://'+dbo.cityUrl(@City) + dbo.propertyURL(@Type, @ID))

SELECT @ID AS Post_ID, @UnixTime AS UnixTime
UPDATE Property_Listings SET Purchase_ID = RIGHT(@UnixTime, 6) WHERE ID = @ID

/* Sending msg notice to users that are tracking this kind of ad */
INSERT INTO Messages (User_To, User_From, Title, Body) VALUES((SELECT [User] FROM Property_Notice WHERE @Rent BETWEEN LowRange AND HighRange AND (Type = @Type) AND (Active = 1)), 2, 'Property Notice', @Body)
END

[k2]

alternatively .. should i use a temp table to store the user id and loop it or something else? suffering from a brain fart and too many things to do. :)

[k2]

figured it out; forgot about the admin queue in place so i had to move this to an update trigger. mofo took 2hrs to get going :)

CREATE TRIGGER [Property_Listings_Update] ON [dbo].[Property_Listings]
FOR UPDATE
AS

DECLARE @Type int, @Approved bit, @City int
SET @Type = (SELECT TOP 1 Type FROM inserted)
SET @Approved = (SELECT TOP 1 Approved FROM inserted)
SET @City = (SELECT TOP 1 City FROM inserted)

/* Sending notices for flats */
IF @Approved = 1 AND (@Type = 2 OR @Type = 6 OR @Type = 7)
BEGIN
CREATE TABLE #Property ([ID] int, City int, Type int, Rent int, Body nvarchar(2000))

INSERT INTO #Property ([ID], City, Type, Rent)
SELECT [ID], City, Type, PriceLow FROM inserted WHERE (Approved = 1) AND (Active = 1) ORDER BY [ID]

UPDATE #Property SET Body = ('You may be interested in the following ad: http://'+dbo.cityUrl(City) + dbo.propertyURL(Type, [ID]))

/* Creating table to hold notices */
CREATE TABLE #PropertyNotice ([ID] int IDENTITY (1, 1) NOT NULL, User_To int, User_From int, Title nvarchar(100), Body nvarchar(500))

INSERT INTO #PropertyNotice (User_To, User_From, Title, Body)
SELECT dbo.Property_Notice.[User] AS User_To, (2) AS User_From, ('Property Notice') AS Title, #Property.Body AS Body
FROM dbo.Property_Notice INNER JOIN
#Property ON dbo.Property_Notice.Type = #Property.Type
WHERE (dbo.Property_Notice.Active = 1) AND (dbo.Property_Notice.City = @City) AND (#Property.Rent BETWEEN dbo.Property_Notice.LowRange AND dbo.Property_Notice.HighRange)

DROP TABLE #Property

/* Starting loop of results to insert into Messages */
DECLARE @ID int, @MaxID int
SET @ID = 1
SET @MaxID = (SELECT COUNT(ID) FROM #PropertyNotice)

WHILE (@ID <= @MaxID)
BEGIN
INSERT INTO Messages (User_To, User_From, Title, Body)
SELECT User_To, User_From, Title, Body FROM #PropertyNotice WHERE ID = @ID
SET @ID = @ID + 1
END

DROP TABLE #PropertyNotice

END

[znaps]

If the user_to field will usually contain more than 1 result, then wouldn't you either select the first of them only, or concatenate them all together (if you don't need to search on the user names from the messages table) ?

[k2]

no, because the temp table could hold more or less records* than the notice table which holds a user's criteria; so inner-joining on the type field held in both allows me to create a list of which user wants to be notified of a property that meets one or more of their criteria. it's a multi-site (city) / property type system so it has to be very flexible.

if 50 properties are approved and only 2 meet user's criteria; those users will receive a msg for each property that falls into the rent range for the type of property it is, and in the proper city; with a url to the listing.

i'm pretty happy with it because i can reuse the code for other sections (life style websites).

this is what the trigger does:

1) admin approves/declines property submissions within the db

2) approved properties are checked against the property_notify table for users that want to be notified of properties that fall into a rent range, property type (share flats, rent, for sale, etc.) for a specific city

3) msgs are prepared

4) msgs are sent to the users with formatted urls, via last insert (email notification might happen, depending on the user's custom settings); otherwise it sits in their inbox.

i'll toss up a graphic of the table schema since it's not that important.

[k2]

the table schema (http://www.attack11.com/schema.gif) is still under development because we're adding some things; but you get the idea. only the property system is present in the pic; user system / msg system schemas aren't present.

[znaps]

I see, gotcha.

[k2]

well i'm back with a problem. i can't figure out a way to get asp or php to retrieve the generated record set for email notification. asp simply won't return a recordset if there's an insert (update) query before a select and php is giving me a very weird error about inserting null values in the body field of the message table. this is what i have now; i modified a few other triggers and added a bit field to the message table to toggle returning the email of the inserted recordset based on the user's settings.

this is my trigger, i think i'm stuck due to server side languages. i know this would work fine in a desktop app, can anyone think of a way to mod this to make it compatible with any serverside language?

CREATE TRIGGER [Property_Listings_Update] ON [dbo].[Property_Listings]
FOR UPDATE
AS

DECLARE @Type int, @Approved bit, @City int
SET @Type = (SELECT TOP 1 Type FROM inserted)
SET @Approved = (SELECT TOP 1 Approved FROM inserted)
SET @City = (SELECT TOP 1 City FROM inserted)

/* Sending notices for flats */
IF @Approved = 1
BEGIN
CREATE TABLE #Property ([ID] int, City int, Type int, Rent int, Body nvarchar(2000))

INSERT INTO #Property ([ID], City, Type, Rent)
SELECT [ID], City, Type, PriceLow FROM inserted WHERE (Approved = 1) AND (Active = 1) ORDER BY [ID]

UPDATE #Property SET Body = ('You may be interested in the following ad: http://'+dbo.cityUrl(City) + dbo.propertyURL(Type, [ID]))

/* Creating table to hold notices */
CREATE TABLE #PropertyNotice ([ID] int IDENTITY (1, 1) NOT NULL, User_To int, User_From int, Title nvarchar(100), Body nvarchar(500))

INSERT INTO #PropertyNotice (User_To, User_From, Title, Body)
SELECT dbo.Property_Notice.[User] AS User_To, (2) AS User_From, ('Property Notice') AS Title, #Property.Body AS Body
FROM dbo.Property_Notice INNER JOIN
#Property ON dbo.Property_Notice.Type = #Property.Type
WHERE (dbo.Property_Notice.Active = 1) AND (dbo.Property_Notice.City = @City) AND (#Property.Rent BETWEEN dbo.Property_Notice.LowRange AND dbo.Property_Notice.HighRange)

DROP TABLE #Property

/* Starting loop of results to insert into Messages */
DECLARE @ID int, @MaxID int
SET @ID = 1
SET @MaxID = (SELECT COUNT(ID) FROM #PropertyNotice)

WHILE (@ID <= @MaxID)
BEGIN
INSERT INTO Messages (User_To, User_From, Title, Body, Email)
SELECT User_To, User_From, Title, Body, (0) FROM #PropertyNotice WHERE ID = @ID
SET @ID = @ID + 1
END

/* Returning email list to webserver to send notification */
SELECT dbo.cityUrl(dbo.AXUsers.City) AS CityUrl, dbo.AXUsers.Email
FROM dbo.AXUsers INNER JOIN
#PropertyNotice ON dbo.AXUsers.[User_ID] = #PropertyNotice.User_To
WHERE (dbo.AXUsers.MsgNotice = 1) ORDER BY dbo.AXUsers.Email

DROP TABLE #PropertyNotice

END

[llbbl]

I'll take a look at it tomorrow. if you can hold out till then :)

[k2]

that's fine, i told my boss as of yesterday it can't be done with the 2 languages. i might look into doing it with mod_perl over the weekend.

[llbbl]

ooooo mod_perl sounds fun. sorry i never got to lookin at this problem.

[k2]

we never went further with it; there was a loop hole in the terms so technically we don't have to send an email for a system notice. :D

[llbbl]

what is the purpose of this program you are trying to write? if you want to send emails to people you have in your database you could just buy a marketing service to send it out for you right.

http://www.google.com/search?q=email+marketing

I mean one of those websites should offer a OPT-in type advertising service if you send them a list of email addresses.

[llbbl]

Or you could setup your own mail list manager in Linux there several packages that would work good.

http://www.emailman.com/unix/mailinglist.html

Linux is gud !!

[k2]

we do that already, this trigger just sends users a system notice to their control panel inbox about content matching their criteria, and returns a recordset of the emails of the users that received the msg; with the intention of sending an email notice of the system notice.

1) msg sent to inbox
2) email sent to user about msg

step 2 doesn't work because of the different bugs in database drivers. php's mssql driver is totally whack with this 1 trigger; which was a let down cause i turn to php to get around the asp bugs re database work.

[k2]

i think we use expresso for our maillist management. the company specializes in corp mail list mangement/shots.

[llbbl]

can't you just send an email out saying you ahve a new message in your inbox so logon and check it stooooopid as soon as you send the msg to the inbox. you can have the user select if they want to recieve such messages via email if you want. that way you can get away from using triggers all together.

[k2]

O_o

that's how it works dude.

[k2]

containg all steps except the final select within 'set nocount on' and 'set nocount off' corrects problem i was having; i didn't think it'd apply to this situation but it does.

[k2]

final (working) version:

CREATE TRIGGER [Property_Listings_Update] ON dbo.Property_Listings
FOR UPDATE
AS

DECLARE @Type int, @Approved bit, @City int
SET @Type = (SELECT TOP 1 Type FROM inserted)
SET @Approved = (SELECT TOP 1 Approved FROM inserted)
SET @City = (SELECT TOP 1 City FROM inserted)

/* Sending notices for flats */
IF @Approved = 1
BEGIN
SET NOCOUNT ON

CREATE TABLE #Property ([ID] int, City int, Type int, Rent int, Body nvarchar(2000))

INSERT INTO #Property ([ID], City, Type, Rent)
SELECT [ID], City, Type, PriceLow FROM inserted WHERE (Approved = 1) AND (Active = 1) ORDER BY [ID]

UPDATE #Property SET Body = ('You may be interested in the following ad: http://'+dbo.cityUrl(City) + dbo.propertyURL(Type, [ID]))

/* Creating table to hold notices */
CREATE TABLE #PropertyNotice ([ID] int IDENTITY (1, 1) NOT NULL, User_To int, User_From int, Title nvarchar(100), Body nvarchar(500))

INSERT INTO #PropertyNotice (User_To, User_From, Title, Body)
SELECT dbo.Property_Notice.[User] AS User_To, (2) AS User_From, ('Property Notice') AS Title, #Property.Body AS Body
FROM dbo.Property_Notice INNER JOIN
#Property ON dbo.Property_Notice.Type = #Property.Type
WHERE (dbo.Property_Notice.Active = 1) AND (dbo.Property_Notice.City = @City) AND (#Property.Rent BETWEEN dbo.Property_Notice.LowRange AND dbo.Property_Notice.HighRange)

DROP TABLE #Property

/* Starting loop of results to insert into Messages */
DECLARE @ID int, @MaxID int
SET @ID = 1
SET @MaxID = (SELECT COUNT(ID) FROM #PropertyNotice)

WHILE (@ID <= @MaxID)
BEGIN
INSERT INTO Messages (User_To, User_From, Title, Body, Email)
SELECT User_To, User_From, Title, Body, (0) FROM #PropertyNotice WHERE ID = @ID
SET @ID = @ID + 1
END

SET NOCOUNT OFF

/* Returning email list to webserver to send notification */
SELECT dbo.cityUrl(dbo.AXUsers.City) AS CityUrl, dbo.AXUsers.Email
FROM dbo.AXUsers INNER JOIN
#PropertyNotice ON dbo.AXUsers.[User_ID] = #PropertyNotice.User_To
WHERE (dbo.AXUsers.MsgNotice = 1) ORDER BY dbo.AXUsers.Email

DROP TABLE #PropertyNotice

END