Update or Insert Trigger - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Update or Insert Trigger

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-08 : 08:10:11

knagag writes "My Main table is like this:
CREATE TABLE [dbo].[Docdetail] (
[DocNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FormType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateofentry] [datetime] NULL ,
[Municipality] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status] [int] NULL ,
[dateofExit] [datetime] NULL ,
[userid] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
In this table status field is variable which changes in every phase of production.
I want to Create a update trigger on status field to enter data(Getdate()) in "Docdetail_Edit" table depends on the status field.
CREATE TABLE [dbo].[Docdetail_edit] (
[DocNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date0] [datetime] NULL ,
[Date1] [datetime] NULL ,
[Date2] [datetime] NULL
) ON [PRIMARY]
GO

For Ex: if status is 0 enter CurrentDate in Date0 field , if status is 1 enter CurrentDate in Date1 field and so on.

I am using this trigger but its not working.

CREATE trigger firstinsert_New1 on docdetail for update,insert
as
If Update(status)
BEGIN
Declare @oldstatus int
Declare @DocNumber char(10)

select @oldstatus=status from deleted
select @DocNumber from docdetail_edit DT ,docdetail DD where DD.DocNo=DT.DocNo
if @oldstatus=0
BEGIN
Update docdetail_edit set date0=getdate()where DocNo=@DocNumber
End
End

I have one more trigger which insert the docno in docdetail_edit table in first insert."

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-08 : 11:32:39

The tables have no primary keys, no fk references.
The Docdetail_edit table is hard coded to store a fixed number of status change dates,
and only the last date for each change of given status.

The trigger logic becomes unecessarily complex and the flexibility suffers from the limitations of the current design.

A more normalized approach would be
1. more flexible
2. easier to implement
3. enable the storage of all status changes to a document.

Example:


-- the tables
create table docs
(
	docno char(10) primary key,
	status int not null
)
go

-- table that is fed by trigger and tracks all statuschanges to docs
create table docs_trackstatus
(
	docno char(10) references docs(docno),
	statusdate datetime not null,
	status int not null,
	primary key(docno,statusdate,status)
)
go

-- trigger for insert, inserts all new docs into the tracking table
create trigger trgInsert_docs_trackstatus on docs for insert as
begin
	insert docs_trackstatus( docno, statusdate, status )
	select inserted.docno, getdate(), inserted.status from inserted
end
go

-- trigger for update, inserts new posts into the tracking table
create trigger trgUpdate_docs_trackstatus on docs for update as
begin
	if update(status)
	insert docs_trackstatus( docno, statusdate, status )
	select inserted.docno, getdate(), inserted.status
	from inserted join deleted on inserted.docno = deleted.docno
	where inserted.status <> deleted.status
end
go

-- insert sample data
insert docs select 'a', 0 union select 'b', 0
update docs set status = status + 1 where docno = 'b'

-- a pivot / select to retrieve the dates for status changes
-- in tabular form
select
	docno,
	max(case status when 0 then statusdate end) as date0,
	max(case status when 1 then statusdate end) as date1,
	max(case status when 2 then statusdate end) as date2
from
	docs_trackstatus
group by
	docno
go


-- cleanup
drop table docs_trackstatus
drop table docs

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources