TRIGGER HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

TRIGGER HELP

Author

Topic

sonygupta
Starting Member

3 Posts

Posted - 2005-10-18 : 00:26:59

Please help me. I need to create a Trigger that would output the name "Account" and the time of change/update to it. I basically need to create a trigger named ActionHistory that would have two columns: TableName and ActionTime. They should tie to my Account table so that when it was changed/updated then the name 'Account' would appear under TableName in my trigger, and the datetime of update would appear under ActionTime. But I just can't figure this one out. So I had this so far, but I KNOW its not right, so can anyone revise this for me?

Create table ActionHistory(TableName varchar(12) NOT NULL, ActionTime datetime NOT NULL)

CREATE TRIGGER trigger_ex2

ON ActionHistory

AFTER INSERT, UPDATE

AS

BEGIN

UPDATE ActionHistory

SET Account = UPPER(LName) WHERE TableName in (SELECT TableName FROM ActionHistory)

SET Loan = UPPER(LName) WHERE IDN in (SELECT TableName FROM ActionHistory)

-------------------------------------------------------

If someone could help me revise my code above, I would be very grateful. I know you guys are the SQL wizzards. Thanks. (by the way, I use SQL 2000 edition)

- SONIA

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-18 : 01:15:35

Didnt understood what extactly you want..

do you want somthing like.. on updating the Account table your ActionHistory should be updated with the name Account and updatedDatetime..

then you have to write the trigger on the table Account and not on ActionHistory..

Confirm if that what you want.

Complicated things can be done by simple thinking

sonygupta
Starting Member

3 Posts

Posted - 2005-10-18 : 13:59:32

Exactly. It sounds stupid, but thats what I've been given. So upon updating the Account table my ActionHistory should be updated with the name "Account" under TableName, and the updated time should be in the Datetime portion.. Can you help me with this code? I've searched the site and looked in my past books, but all I got so far is the codes I gave you earlier... which I KNOW are not right since they don't work when I execute them. help!!

- Thanks, Sonia

anuj164
Starting Member

49 Posts

Posted - 2005-10-18 : 14:58:03

try:
CREATE TRIGGER [test1] ON [dbo].[test]
FOR INSERT, update
AS
begin
Insert into testaudit Select acc, amt, getdate() from inserted
End

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-19 : 02:08:25

hope this helps you..

Create Trigger TrgName On
Accounts
For Insert ,Update
As
Begin
Insert ActionHistory
Select 'Account',GetDate()
End

Complicated things can be done by simple thinking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources