Trigger Help
-
I have a ASP page that inserts information into table "A"
What normaly happens is when a new entry into the database is made we bring up that information and email it to a group that it belongs to from table "B" according to the Group ID that is associated with that Customer.
I have created a new Table "C" with only one Field "autosend" Yes or No.
In the admin section there will be the option to change the field value in "autosend" to "Yes or No"
I need a trigger that when a new record is inserted into Table"A" it will check Table"C" to see if the value is "yes" and than check the new record in Table "A" for the Group ID number and add to that record the email associated with that Group ID from Table "B"
Is there anyone that can help me with this?
-
That seems pretty straighforward. Here's my example tables and the trigger. I used a bit for the AutoSend field instead of Yes/No but you can adjust for that.
--Create example tables
create table A (id int identity(1,1), groupid int, emailaddress varchar(255)) create table B (groupid int, emailaddress varchar(255)) create table C (AutoSend bit default(0))
insert into C values (1) insert into B (groupid, emailaddress) values (1, 'somebody@somewhere.com') insert into B (groupid, emailaddress) values (2, 'somebodyelse@somewhere.com')
CREATE TRIGGER OnInsertA ON [dbo].[A] FOR INSERT AS
UPDATE A SET A.EmailAddress = B.EmailAddress FROM A JOIN INSERTED ON A.ID = INSERTED.ID JOIN B ON A.GroupID = B.GroupID JOIN C ON C.AutoSend <> 0
JR
-
The above works great
...Thank You...Can we now take it a couple steps further...
Below is the code with the proper Table names..
CREATE TRIGGER autosend ON [dbo].[ONSA_NSS]
FOR INSERT
AS
UPDATE ONSA_NSS SET ONSA_NSS.Email = DL_list.DL FROM ONSA_NSS
UPDATE ONSA_NSS SET ONSA_NSS.datesent = fn(date)
UPDATE ONSA_NSS SET ONSA_NSS.onsaempy = '010101'
JOIN INSERTED ON ONSA_NSS.Log_ID = INSERTED.Log_ID
JOIN DL_list ON ONSA_NSS.district = DL_list.district
JOIN C ON C.AutoSend <> 0
exec master.dbo.xp_smtp_sendmail @to = ONSA_NSS.Email,
@subject = N'This job AutoSent',
@message = N'Body of the Message Here',
@priority = N'High'
My main table "ONSA_NSS" formely tbl "A" has some other fields that need to be updated when the email address is entered. I have highlighted the new line in RED...is this the proper code to updat other fields in my main table. Now secondly I need this trigger to also send a email out to the address we just inserted into the Main Table....I have placed this code under the JOIN C on C.Autosend <> 0 line...Is this the proper place for this.
So basicly every time a new record that is entered into the Main Table and Autosent is set to '1' this trigger will run and a email will be sent to the ONSA_NSS.Emaill address.
Thank You Very much for your help.
-
I'd be wary about sending the message from within the trigger - if your mail goes down, the entire insert will fail. I keep a table with fields for recipient, subject, message, copy recipient etc and insert into it from my triggers. I then have a job running every 10 minutes which scans for new records in the message table, sends the emails and then flags them as sent. This way, even if your email system fails, you keep a record of all the messages and can resend them later.
--
Scott -
Scott has a very good point. With a scheduled job, you can at least test to see if the job completed and then try again on the next run (using some kind of sent flag).
Couple adjustments on your syntax:
CREATE TRIGGER autosend ON [dbo].[ONSA_NSS] FOR INSERT AS
UPDATE ONSA_NSS SET ONSA_NSS.Email = DL_list.DL, ONSA_NSS.datesent = fn(date), ONSA_NSS.onsaempy = '010101' FROM ONSA_NSS JOIN INSERTED ON ONSA_NSS.Log_ID = INSERTED.Log_ID JOIN DL_list ON ONSA_NSS.district = DL_list.district JOIN C ON C.AutoSend <> 0
declare @emailaddress varchar(255) SELECT TOP 1 @emailaddress = ONSA_NSS.Email FROM ONSA_NSS JOIN INSERTED ON ONSA_NSS.Log_ID = INSERTED.Log_ID
exec master.dbo.xp_smtp_sendmail @to = @emailaddress, @subject = N'This job AutoSent', @message = N'Body of the Message Here', @from = N'someone@email.com', @server =N'13.000.0.222', @priority = N'High'
You can't just pass a field name to xp_smtp_sendmail, you have to pass it a distinct value. However, the above is assuming that only one record will be inserted at once. If multiple records are inserted in batch, the trigger will only fire once and the message will only go to the first address. It gets a bit trickier with multiple records.
JR
-
I am getting a error with this line.
ONSA_NSS.datesent = fn(date),
What I want to do is insert the Date time into this field..what would be the proper command to do this
-
ONSA_NSS.datesent = GETDATE()
-
Here is my final code..This works...Let me know if you can see a problem with this...Not too much worry about the mailserver going down or multiply entries being done at once. The only part of the code tha I really dont understand is the "Select Top 1"
Thank You very much for all your help
CREATE TRIGGER autosend ON [dbo].[ONSA_NSS]
FOR INSERT
AS
BEGIN
UPDATE ONSA_NSS SET
ONSA_NSS.Email = DL_list.DL,
ONSA_NSS.datesent = GETDATE()
FROM ONSA_NSS
JOIN INSERTED ON ONSA_NSS.Log_ID = INSERTED.Log_ID
JOIN DL_list ON ONSA_NSS.district = DL_list.district
JOIN C ON C.AutoSend <> 0
END
Declare @emailaddress varchar(255)
SELECT TOP 1
@emailaddress = ONSA_NSS.email FROM ONSA_NSS
JOIN INSERTED ON ONSA_NSS.Log_ID = INSERTED.Log_ID
Declare @ID NVARCHAR (4000)
SELECT @ID = (SELECT 'A new Escalation has been created.' + '
' + 'Please use the link below to accept the log' + '
' + '
' + 'Log Number :' + Log_ID + '
'
+ 'Serial Number :' + serial +'
' + 'Company Name:' + Company +'
' +'----------------------------------------------------------------------' +'
' + 'Address :' + address + '
' + 'City : ' + address + '
' + 'State : ' + address + '
' + 'Zip : ' + zipcode + '
' + '----------------------------------------------------------------------' + '
' + 'Contact Name : ' + contactname + '
' + 'Contact Phone : ' + contactphone + '
' + 'Log Details : ' + '
' + details +'
' + '
' + 'To accept this LOG please use the link below' + '
' + 'http://13.000.49.1/escal/accept.asp?Log_ID=' + Log_ID FROM Inserted)
Declare @SUBJ NVARCHAR (60)
SELECT @SUBJ = (SELECT 'ESCALATION-- ' + CBUCity + '---' + zipcode FROM Inserted)
--- Send Email to DL
exec master.dbo.xp_smtp_sendmail @to = @emailaddress,
@subject = @SUBJ,
@message = @ID,
@from = someone@xerox.com',
@server =N'13.00.0.000',
@priority = N'High'
-
The TOP 1 was because INSERTED could technically return multiple rows and you can't assign the values of rows to a single variable.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply