CODEWITHSUNDEEP
sqlsql-server-2008triggers
Anurag Jain
Anurag Jain

Reputation: 1389

Trigger on update of an attribute of a table in SQL Server?

I created computer audit application. When I ran my application, it shows computer accessories on browser like computerName, osVersion, lastAudit, model, totalMemory, processor, userName.

I have created a database in SQL Server 2008 with one table Computers. When a value is inserted into that table, I need to update the table value in the column. In an attempt to try this, I'm using a trigger. However, I do not fully understand how triggers work.

Can someone please show me how to accomplish this.

My table has these columns:

id, computerName, osVersion, lastAudit, model, totalMemory, processor, userName

I know that in this code something wrong or missing but I am not able to complete this. Please help me in this regard.

  CREATE TRIGGER update_trigger
  ON computers
  AFTER UPDATE
   AS 
   BEGIN
declare @id as int
declare @computerName as varchar(100)
declare @osVersion as varchar(100)
declare @lastAudit as datetime
declare @model as varchar(100)
declare @totalMemory float
declare @processor as varchar(100)
declare @userName as varchar(100)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
if update(id)
BEGIN
    insert into computers values(@id,@computerName,@osVersion,@lastAudit,@model,
                            @totalMemory,@processor,@userName,'Update')

SET NOCOUNT ON;

    END
    GO

Upvotes: 1

Views: 3202

Answers (1)

marc_s
marc_s

Reputation: 754508

If you want to simply update one or more columns of your existing table when new rows are being inserted (or when they are updated? Not quite clear...), try a trigger like this:

CREATE TRIGGER trigUpdateTable
ON dbo.Computers
AFTER INSERT  -- or AFTER UPATE or AFTER INSERT, UPDATE
AS 
BEGIN
  -- do whatever you want to do on INSERT and/or UPDATE
  UPDATE 
      dbo.Computers
  SET 
      LastAudit = GETDATE()
  FROM 
      dbo.Computers c
  INNER JOIN 
      Inserted i ON c.id = i.id

One very important point to remember: SQL Server triggers are not called per row that is affected - but per statement, and if your INSERT or UPDATE statement affects multiple rows, you'll have multiple entries in the Inserted pseudo table and you need to be able to deal with that fact in your trigger

Upvotes: 1

Related Questions