Board index MS SQL Server

Urgent help with Triggers in MS-SQL 2000

Urgent help with Triggers in MS-SQL 2000

Post by jq » Sat, 23 Nov 2002 05:44:12



Hello and thank you in advance for any help!  I've got to create an
audit trail of all changes to a database.  After perusing this group
and the books I've got, it looks like a set of archive tables &
triggers are the way to go.  But I've never written any triggers and
I'm pretty marginal on Transact-SQL.  Below are my tables & the
triggers I've just written; the syntax checks but I'm still not sure I
know what I'm doing.

I want to copy the entire row into an archive table after any insert
or update to the original table.  I've got no PKs/ FKs/ indices/
constraints on the archive table; column datatypes and lengths match
between tables.  (I know it will get huge, but I'll deal with that.)  
 There won't be any deletes to worry about for now, and only one row
will be updated at a time (manual data entry from a web front end).
I've added a timestamp column to the archive table, and I'd like to
include an "action taken" as part of the archive.

Will these triggers do what I need?

CREATE TABLE [dbo].[tbl_Base1] (
        [Base1ID] [smallint] IDENTITY (1, 1) NOT NULL ,
        [Base1Type] [varchar] (25) NOT NULL ,
        [Base1ItemDescr] [varchar] (50) NOT NULL ,
        [Base1Group] [varchar] (25) NULL ,
        [Status] [bit] NOT NULL ,
        [UserID] [varchar] (10) NOT NULL ,
        [DeptID] [smallint] NOT NULL ,
        [UpdateDt] [datetime] NOT NULL ,
        [UpdateBy] [varchar] (100) NOT NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[arch_tbl_Base1] (
        [Base1ID] [smallint] NOT NULL ,
        [Base1Type] [varchar] (25) NOT NULL ,
        [Base1ItemDescr] [varchar] (50) NOT NULL ,
        [Base1Group] [varchar] (25) NULL ,
        [Status] [bit] NOT NULL ,
        [UserID] [varchar] (10) NOT NULL ,
        [DeptID] [smallint] NOT NULL ,
        [UpdateDt] [datetime] NOT NULL ,
        [UpdateBy] [varchar] (100) NOT NULL ,
        [ActionTaken] [varchar] (15) NOT NULL ,
        [TxnSeq] [timestamp] NOT NULL
) ON [PRIMARY]
GO

CREATE TRIGGER trgInsert_tblBase1 ON [dbo].[tbl_Base1]
FOR INSERT
AS
INSERT INTO arch_tbl_Base1 (Base1ID, Base1Type, Base1ItemDescr,
Base1Group, Status, UserID, DeptID, UpdateDt, UpdateBy, ActionTaken)
SELECT Base1ID, Base1Type, Base1ItemDescr, Base1Group, Status, UserID,
DeptID, UpdateDt, UpdateBy, 'Insert' AS ActionTaken
FROM INSERTED

CREATE TRIGGER trgUpdate_tblBase1 ON [dbo].[tbl_Base1]
FOR UPDATE
AS
INSERT INTO arch_tbl_Base1 (Base1ID, Base1Type, Base1ItemDescr,
Base1Group, Status, UserID, DeptID, UpdateDt, UpdateBy, ActionTaken)
SELECT Base1ID, Base1Type, Base1ItemDescr, Base1Group, Status, UserID,
DeptID, UpdateDt, UpdateBy, 'Update' AS ActionTaken
FROM UPDATED

Thanks again!
jamileh

 
 
 
Top

Urgent help with Triggers in MS-SQL 2000

Post by Dan Guzma » Sat, 23 Nov 2002 11:11:03


The insert trigger should work but the update trigger also needs to
select from the INSERTED table to get the after image of the updated
row.  In an update trigger, the DELETED table contains the before images
and the INSERTED table contains the after images.  There is no UPDATED
table.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:> Hello and thank you in advance for any help!  I've got to create an
> audit trail of all changes to a database.  After perusing this group
> and the books I've got, it looks like a set of archive tables &
> triggers are the way to go.  But I've never written any triggers and
> I'm pretty marginal on Transact-SQL.  Below are my tables & the
> triggers I've just written; the syntax checks but I'm still not sure I
> know what I'm doing.

> I want to copy the entire row into an archive table after any insert
> or update to the original table.  I've got no PKs/ FKs/ indices/
> constraints on the archive table; column datatypes and lengths match
> between tables.  (I know it will get huge, but I'll deal with that.)
>  There won't be any deletes to worry about for now, and only one row
> will be updated at a time (manual data entry from a web front end).
> I've added a timestamp column to the archive table, and I'd like to
> include an "action taken" as part of the archive.

> Will these triggers do what I need?

> CREATE TABLE [dbo].[tbl_Base1] (
> [Base1ID] [smallint] IDENTITY (1, 1) NOT NULL ,
> [Base1Type] [varchar] (25) NOT NULL ,
> [Base1ItemDescr] [varchar] (50) NOT NULL ,
> [Base1Group] [varchar] (25) NULL ,
> [Status] [bit] NOT NULL ,
> [UserID] [varchar] (10) NOT NULL ,
> [DeptID] [smallint] NOT NULL ,
> [UpdateDt] [datetime] NOT NULL ,
> [UpdateBy] [varchar] (100) NOT NULL ,
> ) ON [PRIMARY]
> GO

> CREATE TABLE [dbo].[arch_tbl_Base1] (
> [Base1ID] [smallint] NOT NULL ,
> [Base1Type] [varchar] (25) NOT NULL ,
> [Base1ItemDescr] [varchar] (50) NOT NULL ,
> [Base1Group] [varchar] (25) NULL ,
> [Status] [bit] NOT NULL ,
> [UserID] [varchar] (10) NOT NULL ,
> [DeptID] [smallint] NOT NULL ,
> [UpdateDt] [datetime] NOT NULL ,
> [UpdateBy] [varchar] (100) NOT NULL ,
> [ActionTaken] [varchar] (15) NOT NULL ,
> [TxnSeq] [timestamp] NOT NULL
> ) ON [PRIMARY]
> GO

> CREATE TRIGGER trgInsert_tblBase1 ON [dbo].[tbl_Base1]
> FOR INSERT
> AS
> INSERT INTO arch_tbl_Base1 (Base1ID, Base1Type, Base1ItemDescr,
> Base1Group, Status, UserID, DeptID, UpdateDt, UpdateBy, ActionTaken)
> SELECT Base1ID, Base1Type, Base1ItemDescr, Base1Group, Status, UserID,
> DeptID, UpdateDt, UpdateBy, 'Insert' AS ActionTaken
> FROM INSERTED

> CREATE TRIGGER trgUpdate_tblBase1 ON [dbo].[tbl_Base1]
> FOR UPDATE
> AS
> INSERT INTO arch_tbl_Base1 (Base1ID, Base1Type, Base1ItemDescr,
> Base1Group, Status, UserID, DeptID, UpdateDt, UpdateBy, ActionTaken)
> SELECT Base1ID, Base1Type, Base1ItemDescr, Base1Group, Status, UserID,
> DeptID, UpdateDt, UpdateBy, 'Update' AS ActionTaken
> FROM UPDATED

> Thanks again!
> jamileh


 
 
 
Top

Urgent help with Triggers in MS-SQL 2000

Post by jq » Sun, 24 Nov 2002 00:24:45


So I need to change the Update trigger to select from the INSERTED
table to add the *new* data to the archive and I'd use the DELETED
table if I wanted to insert the *old* info.  Yes, that does help quite
a bit in understanding how these work.

I realized last night that I will need to audit deletes on one table.
If I've got this straight, I should add the same trigger edited to FOR
DELETE, selected from the DELETED table, and with 'DELETED' as the
ActionTaken  -- that would record in the audit table what data was
deleted.  Yes?

By jove, I think I've got it!

Many thanks!     j


> The insert trigger should work but the update trigger also needs to
> select from the INSERTED table to get the after image of the updated
> row.  In an update trigger, the DELETED table contains the before images
> and the INSERTED table contains the after images.  There is no UPDATED
> table.

> --
> Hope this helps.

> Dan Guzman
> SQL Server MVP

> -----------------------
> SQL FAQ links (courtesy  Neil Pike):

> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> -----------------------



> > Hello and thank you in advance for any help!  I've got to create an
> > audit trail of all changes to a database.  After perusing this group
> > and the books I've got, it looks like a set of archive tables &
> > triggers are the way to go.  But I've never written any triggers and
> > I'm pretty marginal on Transact-SQL.  Below are my tables & the
> > triggers I've just written; the syntax checks but I'm still not sure I
> > know what I'm doing.

> > I want to copy the entire row into an archive table after any insert
> > or update to the original table.  I've got no PKs/ FKs/ indices/
> > constraints on the archive table; column datatypes and lengths match
> > between tables.  (I know it will get huge, but I'll deal with that.)
> >  There won't be any deletes to worry about for now, and only one row
> > will be updated at a time (manual data entry from a web front end).
> > I've added a timestamp column to the archive table, and I'd like to
> > include an "action taken" as part of the archive.

> > Will these triggers do what I need?

> > CREATE TABLE [dbo].[tbl_Base1] (
> > [Base1ID] [smallint] IDENTITY (1, 1) NOT NULL ,
> > [Base1Type] [varchar] (25) NOT NULL ,
> > [Base1ItemDescr] [varchar] (50) NOT NULL ,
> > [Base1Group] [varchar] (25) NULL ,
> > [Status] [bit] NOT NULL ,
> > [UserID] [varchar] (10) NOT NULL ,
> > [DeptID] [smallint] NOT NULL ,
> > [UpdateDt] [datetime] NOT NULL ,
> > [UpdateBy] [varchar] (100) NOT NULL ,
> > ) ON [PRIMARY]
> > GO

> > CREATE TABLE [dbo].[arch_tbl_Base1] (
> > [Base1ID] [smallint] NOT NULL ,
> > [Base1Type] [varchar] (25) NOT NULL ,
> > [Base1ItemDescr] [varchar] (50) NOT NULL ,
> > [Base1Group] [varchar] (25) NULL ,
> > [Status] [bit] NOT NULL ,
> > [UserID] [varchar] (10) NOT NULL ,
> > [DeptID] [smallint] NOT NULL ,
> > [UpdateDt] [datetime] NOT NULL ,
> > [UpdateBy] [varchar] (100) NOT NULL ,
> > [ActionTaken] [varchar] (15) NOT NULL ,
> > [TxnSeq] [timestamp] NOT NULL
> > ) ON [PRIMARY]
> > GO

> > CREATE TRIGGER trgInsert_tblBase1 ON [dbo].[tbl_Base1]
> > FOR INSERT
> > AS
> > INSERT INTO arch_tbl_Base1 (Base1ID, Base1Type, Base1ItemDescr,
> > Base1Group, Status, UserID, DeptID, UpdateDt, UpdateBy, ActionTaken)
> > SELECT Base1ID, Base1Type, Base1ItemDescr, Base1Group, Status, UserID,
> > DeptID, UpdateDt, UpdateBy, 'Insert' AS ActionTaken
> > FROM INSERTED

> > CREATE TRIGGER trgUpdate_tblBase1 ON [dbo].[tbl_Base1]
> > FOR UPDATE
> > AS
> > INSERT INTO arch_tbl_Base1 (Base1ID, Base1Type, Base1ItemDescr,
> > Base1Group, Status, UserID, DeptID, UpdateDt, UpdateBy, ActionTaken)
> > SELECT Base1ID, Base1Type, Base1ItemDescr, Base1Group, Status, UserID,
> > DeptID, UpdateDt, UpdateBy, 'Update' AS ActionTaken
> > FROM UPDATED

> > Thanks again!
> > jamileh

 
 
 
Top

Urgent help with Triggers in MS-SQL 2000

Post by Dan Guzma » Sun, 24 Nov 2002 00:34:14


Yes, I do think you got it!  :-)

BTW, if you only need the 'old' images, you can combing the UPDATE and
DELETE triggers into a single one since they have the same code:

CREATE TRIGGER trgUpdateOrDelete_tblBase1 ON [dbo].[tbl_Base1]
FOR UPDATE, DELETE
AS
...

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


> So I need to change the Update trigger to select from the INSERTED
> table to add the *new* data to the archive and I'd use the DELETED
> table if I wanted to insert the *old* info.  Yes, that does help quite
> a bit in understanding how these work.

> I realized last night that I will need to audit deletes on one table.
> If I've got this straight, I should add the same trigger edited to FOR
> DELETE, selected from the DELETED table, and with 'DELETED' as the
> ActionTaken  -- that would record in the audit table what data was
> deleted.  Yes?

> By jove, I think I've got it!

> Many thanks!     j




- Show quoted text -

> > The insert trigger should work but the update trigger also needs to
> > select from the INSERTED table to get the after image of the updated
> > row.  In an update trigger, the DELETED table contains the before
images
> > and the INSERTED table contains the after images.  There is no
UPDATED
> > table.

> > --
> > Hope this helps.

> > Dan Guzman
> > SQL Server MVP

> > -----------------------
> > SQL FAQ links (courtesy  Neil Pike):

> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > -----------------------



> > > Hello and thank you in advance for any help!  I've got to create
an
> > > audit trail of all changes to a database.  After perusing this
group
> > > and the books I've got, it looks like a set of archive tables &
> > > triggers are the way to go.  But I've never written any triggers
and
> > > I'm pretty marginal on Transact-SQL.  Below are my tables & the
> > > triggers I've just written; the syntax checks but I'm still not
sure I
> > > know what I'm doing.

> > > I want to copy the entire row into an archive table after any
insert
> > > or update to the original table.  I've got no PKs/ FKs/ indices/
> > > constraints on the archive table; column datatypes and lengths
match
> > > between tables.  (I know it will get huge, but I'll deal with
that.)
> > >  There won't be any deletes to worry about for now, and only one
row
> > > will be updated at a time (manual data entry from a web front
end).
> > > I've added a timestamp column to the archive table, and I'd like
to
> > > include an "action taken" as part of the archive.

> > > Will these triggers do what I need?

> > > CREATE TABLE [dbo].[tbl_Base1] (
> > > [Base1ID] [smallint] IDENTITY (1, 1) NOT NULL ,
> > > [Base1Type] [varchar] (25) NOT NULL ,
> > > [Base1ItemDescr] [varchar] (50) NOT NULL ,
> > > [Base1Group] [varchar] (25) NULL ,
> > > [Status] [bit] NOT NULL ,
> > > [UserID] [varchar] (10) NOT NULL ,
> > > [DeptID] [smallint] NOT NULL ,
> > > [UpdateDt] [datetime] NOT NULL ,
> > > [UpdateBy] [varchar] (100) NOT NULL ,
> > > ) ON [PRIMARY]
> > > GO

> > > CREATE TABLE [dbo].[arch_tbl_Base1] (
> > > [Base1ID] [smallint] NOT NULL ,
> > > [Base1Type] [varchar] (25) NOT NULL ,
> > > [Base1ItemDescr] [varchar] (50) NOT NULL ,
> > > [Base1Group] [varchar] (25) NULL ,
> > > [Status] [bit] NOT NULL ,
> > > [UserID] [varchar] (10) NOT NULL ,
> > > [DeptID] [smallint] NOT NULL ,
> > > [UpdateDt] [datetime] NOT NULL ,
> > > [UpdateBy] [varchar] (100) NOT NULL ,
> > > [ActionTaken] [varchar] (15) NOT NULL ,
> > > [TxnSeq] [timestamp] NOT NULL
> > > ) ON [PRIMARY]
> > > GO

> > > CREATE TRIGGER trgInsert_tblBase1 ON [dbo].[tbl_Base1]
> > > FOR INSERT
> > > AS
> > > INSERT INTO arch_tbl_Base1 (Base1ID, Base1Type, Base1ItemDescr,
> > > Base1Group, Status, UserID, DeptID, UpdateDt, UpdateBy,
ActionTaken)
> > > SELECT Base1ID, Base1Type, Base1ItemDescr, Base1Group, Status,
UserID,
> > > DeptID, UpdateDt, UpdateBy, 'Insert' AS ActionTaken
> > > FROM INSERTED

> > > CREATE TRIGGER trgUpdate_tblBase1 ON [dbo].[tbl_Base1]
> > > FOR UPDATE
> > > AS
> > > INSERT INTO arch_tbl_Base1 (Base1ID, Base1Type, Base1ItemDescr,
> > > Base1Group, Status, UserID, DeptID, UpdateDt, UpdateBy,
ActionTaken)
> > > SELECT Base1ID, Base1Type, Base1ItemDescr, Base1Group, Status,
UserID,
> > > DeptID, UpdateDt, UpdateBy, 'Update' AS ActionTaken
> > > FROM UPDATED

> > > Thanks again!
> > > jamileh

 
 
 
Top

1. help,help,help,help,help,help,help,help,help,help,help,help,help,

Hi,
I have a small problem, and if you know the answer I would really be
thankful for your help.
I have an access database which is called "Client.mdb"
on a form, I displayed a dbcombox for the costumer's name
a textbox for the address
a textbox for the city
a textbox for the state
and a textbox for the zipcode

on all my textboxes and the dbcombobox I have set their Datasource and
Datafield properties to Data1,
when I run the program the information on the first record of the data shows
on the form,

but what I need to do is, when I change the costumer's name on the
dbcombobox (which displays all of the costumer's names on it) I want the
textboxes to show the address, city, state, and zipcode of the costumer I
have choosen,

Thank you in advance,
Enrique G Lugo

2. Wa-Washington-104484--ORACLE-DBA Skills-Stored Procedures-Triggers-Performance T

3. Connecting to Sql Server from asp after upgrade from ms sql 7 to ms sql 2000

4. data control, forms, calculated fields?

5. Pervasive SQL 2000 Connection from MS SQL 2000

6. Who Pays for a Bug?

7. Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,

8. Help !!!!! with datagrid

9. Migration from Pervasive SQL 2000 to MS-SQL Server 2000 using ADO

10. MS Access 97 SQL to MS SQL SQL SQL SQL

11. MS SQL 2000 to MS SQL 6.5

12. MS SQL 2000 vs MS SQL 7

13. Documents about the changes from MS SQL 6.5 to MS SQL 2000


All times are UTC
Board index