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.
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 )
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
This is the final working Code
CREATE TRIGGER [Last_Modified_products_ne w] 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
CREATE TRIGGER [Last_Modified_products_ne
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
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),pmodif
from products
where pid=...
hth