Link to home
Start Free TrialLog in
Avatar of awestry
awestry

asked on

Date Last Modified SQL Trigger & Display Script

I have a MS SQL Database. With a table named Products. Field name pID (pri Key) and pModified (smalltimedate). I have approx. 2000 products entered. I have to edit each product with HTML in my site admin section. currently it looks like this on the product listing*** 28451 African lady*** . I would for it to be     *** 28451 African lady last modified 11/24/04 6:42pm***. This is to show which products have been modified. I am new to SQL Database, using EMS SQL Manager and frontpage 2003. It is an ASP Site. I would like to know how to create the trigger and a small script to show the last modified time/date on the webpage.
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

trigger

create trigger Trg_Upd_Products
on table dbo.products
for UPDATE

as

Update Products
 set Pmodified = getdate ()
 Where Products as P
Inner Joined Inserted as I
 on P.pid=I.pid

go


to display something like

select  '***' + convert(varchar(10),pid) + ' ' [Name]
              + coalesce(' ' + convert(varchar(20),pmodified),'') + ' ***'
from products
where pid=...

hth
 
Avatar of awestry
awestry

ASKER

CREATE TRIGGER [Trg_Upd_Products] ON [xxxxx_xxxx].[products]
FOR INSERT, UPDATE
AS
BEGIN
  set pModified = getdate ()
 Where Products as P
Inner Joined Inserted as I
 on P.pID=I.pid

END
GO

I tried the code above I get the error :Line 5: Incorrect syntax near '='
I am using EMS SQL Manager New Trigger Dialog.

note: ( xxxx was used on line 1 for forum post db password is correct )
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of awestry

ASKER

CREATE TRIGGER [Trg_Upd_Products] ON [xxxxx_xxxx].[products]
FOR INSERT, UPDATE
AS
BEGIN
  set pModified = getdate ()
 Where Products as P
Inner Join Inserted as I
 on P.pID=I.pid

END
GO

I tried the code above I still get the same error :Line 5: Incorrect syntax near '='
could the error be in the set pModified = getdate () statement

Avatar of awestry

ASKER

This is the final working Code


CREATE TRIGGER [Last_Modified_products_new] ON [awestry_giftsu].[products]
FOR UPDATE
AS
UPDATE products
SET pModified = GETDATE()
FROM products ytn
INNER JOIN inserted i ON ytn.pID = i.pID
GO

Thanks for the help
AW