how to update record in log table when record exists other wise insert using trigger

Question

i have a table Wind

CREATE TABLE WIND (WindApp VARCHAR(10),Name VARCHAR(10),Isstatus CHAR(1))

INSERT INTO [mohan].[dbo].[wind]([WindApp] ,[Name],[ISStatus])
Select 'WP1000','Mohan','Y'

MY LOGTABLE :

CREATE TABLE [dbo].[logtable](
    [App] [varchar](10) NULL,
    [Name] [varchar](10) NULL,
    [ISStatus] [char](1) NULL,
    [Dated] [datetime] NULL
) ON [PRIMARY]


**update wind set isstatus = 'N'** 

CREATE TRIGGER trgAfterUpdate ON [dbo].[wind] 
FOR UPDATE
AS

    declare @solarapp varchar(100);
    declare @name varchar(100);
    declare @isstatus varchar(1);

    select @solarapp=i.WindApp from inserted i; 
    select @name=i.name from inserted i;    
    select @isstatus=i.ISStatus from inserted i;    


    insert into logtable(App,name,ISStatus,Dated) 
    values(@solarapp,@name,@isstatus,getdate());

    PRINT 'AFTER UPDATE Trigger fired.'
GO

Now i have created a trigger After Update it will insert into log table after record inserted into Wind table.But i want to insert only when it is new record other wise it need to get updated or deleted to insert again new record

If you just have a single audit `log` record for changes to the `wind` table, then how will you be able to track previous updates made to the table? — StuartLC, Jun 05 '15 at 18:55
in my log table i just want to maintain latest based on the WindApp = WP100 if it's new record just insert ,existing on update in log table @StuartLC — mohan111, Jun 05 '15 at 19:00
That is a whole lot of overhead when you could but put date in [mohan] and a View to get the latest. It would be less overhead and not you have date for every record. — paparazzo, Jun 05 '15 at 19:08
can you justify the suggestion actually i didn't get you what you are suggesting@Blam — mohan111, Jun 05 '15 at 19:12
Why would you ever update a log table. The point of a log table is to record actions, if you edit it you lose the historical details and accuracy. — Sean Lange, Jun 05 '15 at 19:22
`inserted` and `deleted` are tables so that they can represent the results of set operations. Designing a trigger on the assumption that it will always process exactly one row is generally a bad plan. If you are absolutely certain there will never be more than one row then _please_ add a check for the number of rows and use `RaIsError` or `Throw` to explicitly inform those who come along later that they have attempted to execute an unacceptable statement. (`if ( select Count(*) from inserted ) > 1 RaIsError( 'FooTable_Insert: No more than one row may be processed.', 25, 42 ) with log`) — HABO, Jun 05 '15 at 20:22

score 0 · Answer 1 · answered Jun 05 '15 at 18:52

0

Use FOR INSERT, not FOR UPDATE on the trigger

answered Jun 05 '15 at 18:52

Bill Gauvey

95
1
5

can you elaborate the purpose of insert in trigger and how can i achieve my result using that – mohan111 Jun 05 '15 at 18:56

how to update record in log table when record exists other wise insert using trigger

1 Answers1