Help! Trying to create a trigger in SQL 2000...

Hello All!

I am trying to create a trigger that upon insertion into my table an email will be sent to that that recipeinent with a image attached ( like a coupon)That comes from a different table, problem is, It will not allow me to send the email ( using xp_sendmail) with the coupon attached. I am using varbinary for the coupon and nvarchar for the rest to be sent, I get an error that Invaild operator for data type. operator equals add, type equals varchar.

Looks basically like this(This is my test tables):

CREATE TRIGGER EileenTest ON OrgCouponTestMain
FOR Insert
AS
declare @.emailaddress varchar(50)
declare @.body varchar(300)
declare @.fname varchar(50)
declare @.coupon varbinary(4000)

if update(emailaddress)
begin

Select
@.emailaddress=(select EmailAddress from OrgCouponTestMain as str),
@.fname=(select EmailAddress from OrgCouponTestMain as str)
@.Coupon=(select OrgCoupon1 from OrgCouponTest2 as image)

SET @.body= 'Thank you' +' '+ @.fname +' '+ ',Here is the coupon you requested' +' ' + @.coupon
exec master.dbo.xp_sendmail
@.recipients = @.emailaddress,
@.subject = 'Coupon',
@.message = @.body
END

Hello my friend,

Try the following: -

SET @.body = 'Thank you ' + @.fname + ', Here is the coupon you requested ' + CAST(@.coupon AS VARCHAR(8000))

Kind regards

Scotty

|||

Hi there,

I've recently developed a functionality at my work place that is very similar to the one you need.

My suggestion is that you make things other way (very much simpler i think):

Instead of coding in T-SQL, you can develop your application in any .NET language. I suggest you to develop a Web Service or an ASP.NET page that retrieves the "destinations" from the SQL Server, loads the coupon and sends it to them.

Then you just have to develop a very simple COM Class that the ONLY thing it does is calling the Web Service (our ASP.NET page) you developed. There is many documentation on the Web on how to call a COM Class from an SQL Trigger / Job.

It's a bit tricky at the beggining but when you figure it out, it's easy as a walk in the park. And remember, when you need to change your application, you just have to change the .NET Web Service that it's called by the COM Class (much more friendly than coding in T-SQL).

Even if this isn't a web server, you can always create a website on localhost to host the service/page.

Hope I've helped you out!

gonzzas

|||

Thank you both for your input, Ask scotty, it didn't work, got the email out but no image, and of course there is that other option, which unless i can figure this out i will be doing instead. Thank you!

|||

So anyone else have an idea? This is what comes up in the email message, notice the symbols instead of the image...

Thank you Eileen, Here is the coupon you requested /

|||

Anyone else have a suggestion? Here is what comes in the email , notice the symbols instead of the image:

Thank you Eileen, Here is the coupon you requested /

|||

Hi,

The problem is focused on how to display the image which saved as Image Data type in database. Because what you get from database directly is binary steam of the image, it can't be displayed in your email directly. What I suggest is pass the steam (and other information ) from Stored Procedure to .NET, and use SqlDataReader to read the image and send the mail to your customer.

HOW to Retrieve an image from sql server and display it in ASP.net using "imagemap or image" ?https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=531518&SiteID=1

Hope it can helps. Thanks.

sql

help with writting a trigger

Hi,
I have 2 tables ''Data' and 'LatestData' with same fields:
- ID,
- TruckNo,
- DateTime,
- Comment.
My application adds data to the Data table. I would like to have a trigger
which automatically updates or adds (if not exists) newly added records into
LatestData table. This trigger should also look at DateTime, and add records
only if they are newer then existing for a TruckNo.
So LatestData table contains only one and newest record per one TruckNo.
Thanks for help.
PrzemoHi
(untesed)
IF NOT EXISTS
(SELECT * FROM LatestData WHERE LatestData.TruckNo=inserted.TruckNo)
INSERT INTO LatestData SELECT * FROM Inserted
IF NOT EXISTS
(SELECT * FROM LatestData WHERE LatestData.ID=inserted.ID)
INSERT INTO LatestData SELECT * FROM Inserted
ELSE
UPDATE LatestData SET col=(SELECT i.col FROM inserted i JOIN deleted d
ON d.ID=i.ID)
WHERE EXISTS
(SELECT * FROM LatestData WHERE LatestData.ID=deleted.ID)
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:85F7771F-96E1-42FB-9B2D-F8EC02BCCED1@.microsoft.com...
> Hi,
> I have 2 tables ''Data' and 'LatestData' with same fields:
> - ID,
> - TruckNo,
> - DateTime,
> - Comment.
> My application adds data to the Data table. I would like to have a trigger
> which automatically updates or adds (if not exists) newly added records
> into
> LatestData table. This trigger should also look at DateTime, and add
> records
> only if they are newer then existing for a TruckNo.
> So LatestData table contains only one and newest record per one TruckNo.
> Thanks for help.
> Przemo|||CREATE TRIGGER [TR1] ON [dbo].[Data]
FOR INSERT, UPDATE, DELETE
AS
IF EXISTS
(SELECT * FROM LatestData,Inserted WHERE LatestData.ID=inserted.ID)
DELETE FROM LatestData WHERE ID IN( SELECT INSERTED.ID FROM
LatestData,Inserted WHERE LatestData.ID=inserted.ID)
INSERT INTO LatestData SELECT * FROM Inserted
---
Hi Uri,
I used your logic only, thanks for that but thought not to mess up with
update. I tested your code but it failed the update part and tehre was few
syntax error also.
The above logic delete updated record from LatestData so we consider it as a
new Insert and not to mess with update.
Thanks,
Sree
"Uri Dimant" wrote:

> Hi
>
> (untesed)
>
> IF NOT EXISTS
> (SELECT * FROM LatestData WHERE LatestData.TruckNo=inserted.TruckNo)
> INSERT INTO LatestData SELECT * FROM Inserted
>
> IF NOT EXISTS
> (SELECT * FROM LatestData WHERE LatestData.ID=inserted.ID)
> INSERT INTO LatestData SELECT * FROM Inserted
> ELSE
> UPDATE LatestData SET col=(SELECT i.col FROM inserted i JOIN deleted d
> ON d.ID=i.ID)
> WHERE EXISTS
> (SELECT * FROM LatestData WHERE LatestData.ID=deleted.ID)
>
>
> "Przemo" <Przemo@.discussions.microsoft.com> wrote in message
> news:85F7771F-96E1-42FB-9B2D-F8EC02BCCED1@.microsoft.com...
>
>

Help with update trigger

Hi all,
I know squat about triggers so was hoping somebody could point me in the
right direction. I wanted to copy an email address field from a salesman
table to a note field in a customer table. Seems easy enough for a one time
update. But I would like to add a trigger to auto-update the customer table
anytime an email address changes in the saleman table or a new salesman
record is added.

Here's my update script (this copies the salesman email address to each of
his customers)
UPDATE CUSTOMERS
SET NOTE_5 = SALESMAN.EMAIL_ADDR
FROM CUSTOMERS INNER JOIN
SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO

How can I turn this into a trigger for automatic updates?

Thanks for any help.rdraider (rdraider@.sbcglobal.net) writes:
> I know squat about triggers so was hoping somebody could point me in the
> right direction. I wanted to copy an email address field from a
> salesman table to a note field in a customer table. Seems easy enough
> for a one time update. But I would like to add a trigger to auto-update
> the customer table anytime an email address changes in the saleman table
> or a new salesman record is added.
> Here's my update script (this copies the salesman email address to each of
> his customers)
> UPDATE CUSTOMERS
> SET NOTE_5 = SALESMAN.EMAIL_ADDR
> FROM CUSTOMERS INNER JOIN
> SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO
>
> How can I turn this into a trigger for automatic updates?

CREATE TRIGGER salesman_tri FOR INSERT, UPDATE ON SALESMAN AS
UPDATE CUSTOMERS
SET NOTE_5 = i.EMAIL_ADDR
FROM CUSTOMERS c
JOIN inserted c.SLSPSN_NO = i.SLSPSN_NO

"inserted" is a virtual table that holds the row that were inserted, or
the after-image of the updated rows.

"deleted" is a sister table that holds deleted rows, or the before-image
of the updated rows.

Note that triggers fires once per statement, so these tables can include
many rows.

You can only access these tables directly in a trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help with UPDATE trigger

I am trying to setup a trigger that sends an email if a field is changed to specific data. The trigger works when ever the field is changed, but I only need an email if the field is changed to 'In Review'
Any help is greatly appreciated.

-- Create the trigger
CREATE TRIGGER reviewntc

--indicate which table the trigger is to be executed on
ON CltDue

--indicate that this an UPDATE Trigger
FOR UPDATE
AS

IF UPDATE(CDSTATUS)
BEGIN
--holds the changes
declare @.CDStatus varchar(40), @.CDClientName varchar (40), @.CDEventDesc varchar (40)
--grabs the data that we need
SELECT @.CDStatus = CDStatus, @.CDClientName = CDClientName, @.CDEventDesc = CDEventDesc
FROM inserted
declare @.rc int, @.mymessage nvarchar(4000), @.mysubject varchar (4000)
SET @.mymessage = N'The '+@.CDClientName+' "'+@.CDEventDesc+'" project has been changed to '+@.CDStatus+''
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'sender@.domain.com',
@.FROM_NAME = N'sender',
@.TO = N'rcpt@.domain.com',
@.subject = N'A project has been changed to "In review"',
@.message = @.mymessage,
@.type = N'text/plain',
@.server = N'email serverl'
select RC = @.rc
END

goYou need to check for the updated value.|||I'm sorry, but I am new to SQL.
Where and how do I insert CHECK|||Got it.

I added an IF
Here is the code I have if anyone needs

-- Drop the trigger if it already exists
IF EXISTS(
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[reviewntc]') AND
OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [reviewntc]
GO

-- Create the trigger
CREATE TRIGGER reviewntc

--indicate which table the trigger is to be executed on
ON CltDue

--indicate that this an UPDATE Trigger
FOR UPDATE
AS

IF UPDATE(CDSTATUS)
BEGIN
set nocount on
--holds the changes
declare @.CDStatus varchar(40), @.CDClientName varchar (40), @.CDEventDesc varchar (40)
--grabs the data that we need
SELECT @.CDStatus = CDStatus, @.CDClientName = CDClientName, @.CDEventDesc = CDEventDesc
FROM inserted
IF @.CDStatus = 'In Review'
BEGIN
declare @.rc int, @.mymessage nvarchar(4000), @.mysubject varchar (4000)
SET @.mymessage = N'The '+@.CDClientName+' "'+@.CDEventDesc+'" project has been changed to '+@.CDStatus+''
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'sender@.domain',
@.FROM_NAME = N'sender',
@.TO = N'rcpt@.domain',
@.subject = N'A project has been changed to "In review"',
@.message = @.mymessage,
@.type = N'text/plain',
@.server = N'email server'
select RC = @.rc

END
END
go