Use Triggers ? - 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 2008 Forums

Transact-SQL (2008)

Use Triggers ?

Author

Topic

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-12 : 09:14:17

I have 3 tables -

TABLE 1#

CREATE TABLE [dbo].[MCP1_CommFlt](
[Fault] [varchar](20) NULL,
[Occurred] [datetime] NULL,
[OccurDate] [date] NULL,
[OccurTime] [time](0) NULL,
[Duration] [time](0) NULL,
[MCPID] [int] NULL
) ON [PRIMARY]

TABLE 2#

CREATE TABLE [dbo].[MCP1_CommFlt_History](
[Fault] [varchar](20) NULL,
[Occurred] [datetime] NULL,
[OccurDate] [date] NULL,
[OccurTime] [time](0) NULL,
[Duration] [time](0) NULL,
[MCPID] [int] NULL
) ON [PRIMARY]

TABLE 3#

CREATE TABLE [dbo].[SortStartEnd](
[RowIdentity] [varchar](20) NULL,
[SOS] [bit] NULL,
[EOS] [bit] NULL,
[SortTime] [datetime] NULL
) ON [PRIMARY]

I need to move move all records from TABLE 1# - MCP1_CommFlt
INTO TABLE 2# - MCP1_CommFlt_HISTORY
When a record like -
INSERT INTO SORTSTARTEND VALUES ('EndOfSort','0','1','2010-05-07 18:00:49.723')

is entered into the TABLE 3# - SortStartEnd.

Any suggestions ?

Thanks,

Alan.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 09:23:43

To move from #1 to #2 means not to copy - right?

I would say the process that is inserting into #3 has to do that.
But anyway:
Create a trigger on #3 that, if the condition is true, deletes a record in #1 with the new feature OUTPUT into #2.

No, you're never too old to Yak'n'Roll if you're too young to die.

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-12 : 09:28:01

So basically, its a 3 step trigger.

If criteria is correct in Table #3 then
Copy all records into Table #2
Delete all records in Table #1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 09:36:19

Basically yes but as I stated before you can use the new feature to have your delete and your insert in only one statement using OUTPUT.
But if you don't like it then you can do your insert #2 and then your delete from #1.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 10:29:30

I cant understand why you need to transfer all the records. or is it like transfer only ones which are in some way related to table3 by means of field(s)?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-12 : 10:37:13

visakh16,

Table #1 is updated every 5 seconds with a minimum of 10 records .. I query and post results every time on an ASP page. This being a manufacturing facility runs 24X7 and almost 360 days.

We need to keep the records for 3 years and thus the history table concept comes into play. the only relation with table #3 is that the Shift is over. that particular field signals the end of the shift.

Does this answer your question ?

Alan.

Kristen
Test

22859 Posts

Posted - 2010-05-12 : 11:05:50

Can you copy all records as they are created (using a trigger) and then just trigger an (extra) delete when the end-of-shift record is seen? (as all records will already be in both tables)

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-12 : 11:07:40

That is what I would like to do .. can you help ?

Kristen
Test

22859 Posts

Posted - 2010-05-12 : 11:34:30

Create AFTER INSERT trigger on the MCP1_CommFlt table that does:


INSERT INTO MCP1_CommFlt_HISTORY
SELECT *
FROM Inserted

although I had a worry. Do the records in MCP1_CommFlt get updated? If so it would be better to copy them at the point when they are deleted, otherwise you will only get the data as-created in the History table.

Create an AFTER DELETE trigger on SortStartEnd:


DELETE CF
FROM MCP1_CommFlt AS CF
     JOIN Deleted AS D
        ON D.SortTime >= CF.Occurred -- Is this the right criteria?

if you DO Update the rows after initial create then BEFORE the delete operation, in the AFTER DELETE trigger, put


INSERT INTO MCP1_CommFlt_HISTORY
SELECT CF.*
FROM MCP1_CommFlt AS CF
     JOIN Deleted AS D
        ON D.SortTime >= CF.Occurred -- Use the same criteria as above

OR

Create an AFTER DELETE trigger on MCP1_CommFlt also that does


INSERT INTO MCP1_CommFlt_HISTORY
SELECT *
FROM Deleted

that will put the record into the History WHENEVER / HOWEVER it comes to be deleted (which may, or may not!, be what you want

You will need to ensure that nested triggers work - i.e. creating the SortStartEnd record will trigger the AFTER INSERT trigger on SortStartEnd, which will delete the rows from MCP1_CommFlt and then the nested AFTER DELETE trigger on MCP1_CommFlt will save the rows to MCP1_CommFlt_HISTORY

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 11:36:04

ok..that makes sense

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-12 : 11:55:38

Yes the rows are updated at times .. specially when the fault exceeds the refresh rate on the input equipment.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources