Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: SQL Trigger

  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: SQL Trigger - ID of record

    I am having a hard time understanding triggers. My goal is to put a trigger on table x where records are UPDATED or DELETED. When this trigger fires I need to take the record ID and put the ID modified record into table y with the date modified. so basically logging the recordid changed with the getDate()

    I don't quite understand how to get the rowid of the modified record.

    Any ideas?
    Last edited by Eric the Red; 03-25-13 at 16:13.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try something like this:

    Code:
    insert into TableY (Id, LogDate)
        select Id, GETDATE() from deleted
    Hope this helps.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Check out the inserted and deleted virtual tables.

    These are ONLY available in your trigger.

    So have a play with a trigger that runs two select statements:
    Code:
    SELECT *
    FROM   inserted;
    
    SELECT *
    FROM   deleted;
    Once you've wrapped your head round this part let us know if you need further help achieving your goal.
    George
    Home | Blog

  4. #4
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by gvee View Post
    Check out the inserted and deleted virtual tables.

    These are ONLY available in your trigger.

    So have a play with a trigger that runs two select statements:
    Code:
    SELECT *
    FROM   inserted;
    
    SELECT *
    FROM   deleted;
    Once you've wrapped your head round this part let us know if you need further help achieving your goal.
    Ahh Thanks! I read that in a tutorial but the words didn't turn blue in Management Studio as a reserved word would. This helps a lot!

  5. #5
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I do that exact same thing with market & taxable values. I have to keep track of the old values in a log file.

    This is a log file I have put together and am trying out on our test server. Most of the procedure is based on ideas from other SQL postings on the web.
    Excuse the formatting. Between SSMS and copy & paste, tabs & spaces get messed up.

    Code:
    CREATE TRIGGER [dbo].[TRValueTrigger]
       ON [dbo].[TRValue]
       AFTER INSERT,UPDATE,DELETE
    AS
    BEGIN
       
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
    	-- Check to see if this Trigger has been disabled
    	declare @CInfo VarBinary(128) = CONTEXT_INFO()
    	
    	declare @Act varchar(6)
    	declare @Del bit = 0
    	declare @Ins bit = 0
    	
    	-- Check to see if this Trigger is a Insert, Update, or Delete
    	if (exists(select top 1 1 from Deleted))  set @Del = 1
    	if (exists(select top 1 1 from Inserted)) set @Ins = 1
    
    	if ((@Ins = 0) and (@Del = 0)) return
    	if ((@Ins = 0) and (@Del = 1)) set @Act = 'DELETE'
    	if ((@Ins = 1) and (@Del = 0)) set @Act = 'INSERT'
    	if ((@Ins = 1) and (@Del = 1)) set @Act = 'UPDATE'
    	
       if (@Act = 'INSERT') Begin
            if (@CInfo = 0x12345) return
    
              INSERT TRLog 
                (Year, Parcel, ChangeType, NewCode, NewSequence, NewAcres, NewMarket, NewTaxable, District)
                (SELECT 
                  i.Year, i.Parcel, i.ChangeType, i.Code, V.Sequence, i.Acres, i.CurrentMarket, i.CurrentTaxable, M.District
                   FROM inserted i
                   LEFT JOIN TRValue  V on V.RIN = i.RIN
                   LEFT JOIN TRMaster M on M.Year = V.Year and M.Parcel = V.Parcel)
       End 
       Else Begin
            if (@Act = 'UPDATE') Begin
                UPDATE xRec
                SET MDateTime = GetDate(),
                    MUser     = (Select case when len(nt_username) > 0 then nt_username else loginame end
    							from sys.sysprocesses where spid = @@spid)
               FROM TRValue xRec
               JOIN inserted i
                 ON i.RIN = xRec.RIN
    
       	 if (@CInfo = 0x12345) return
                
    	    INSERT into TRLog
              (Year, Parcel, ChangeType, OldCode, OldSequence, OldAcres, OldMarket, OldTaxable,
               NewCode, NewSequence, NewAcres, NewMarket, NewTaxable, District)
    	       (SELECT
    	             i.Year, i.Parcel, i.ChangeType, d.Code, d.Sequence, d.Acres, d.CurrentMarket, d.CurrentTaxable,
    	             i.Code, i.Sequence, i.Acres, i.CurrentMarket, i.CurrentTaxable, M.District
    	         FROM inserted i
    	         JOIN deleted  d on d.RIN = i.RIN
    	         JOIN TRMaster M on M.Year = i.Year and M.Parcel = i.Parcel
    	        WHERE (d.Acres          <> i.Acres          or
    	               d.CurrentMarket  <> i.CurrentMarket  or
    	               d.CurrentTaxable <> i.CurrentTaxable or
    	               d.CurrentTaxAmt  <> i.CurrentTaxAmt) and
    	               d.RIN            =  i.RIN)
       	   End
       Else Begin
           if (@CInfo = 0x12345) return
           if (@Act = 'DELETE') Begin
             INSERT TRLog 
                (Year, Parcel, ChangeType, OldCode, OldSequence, OldAcres, OldMarket, OldTaxable, District)
                (SELECT
                   d.Year, d.Parcel, d.ChangeType, d.Code, d.Sequence,d.Acres,d.CurrentMarket,d.CurrentTaxable, M.District
                  FROM deleted d
                  JOIN TRMaster M on M.Year = d.Year and M.Parcel = d.Parcel)        
           end
        end
     end
     
    END

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There's a trigger just begging to be rewritten.
    Linksup, you need to dump all that conditional logic. Inner joins will perform the same effect, with more efficiency.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    This is not the way to do an audit.

    DDL would help. We hate those old 1970's Sybase dialect triggers and want to get rid of them with DRI actions. We never do auditing inside the schema being audited; it is illegal and guarantees that a massive failure will also destroy the audit trail with the database.

    This looks like assembly language coding with its bit flags and not SQL. Why write:

    EXISTS(SELECT TOP 1 1 FROM DELETED)
    and not
    EXISTS(SELECT * FROM DELETED)
    ?

    Think about why you would want to create a constant, sort the table and then pull it out (the optimizer is smarter than that, but that is your mindset)

    YEAR, SEQUENCE, etc. are all reserved words in SQL and too vague to be a data element name. You are still writing 1960's assembler in T-SQL. The first part of your procedural code can use one statement with predicates in place of low-level bit flags in procedural code.

    SET @action_string
    = CASE WHEN (EXISTS(SELECT * FROM DELETED)
    AND EXISTS(SELECT * FROM INSERTED)
    THEN 'update'
    WHEN (EXISTS(SELECT * FROM DELETED)
    AND NOT EXISTS(SELECT * FROM INSERTED)
    THEN 'delete'
    WHEN (NOT EXISTS(SELECT * FROM DELETED)
    AND EXISTS(SELECT * FROM INSERTED)
    THEN 'inserted'
    ELSE CAST (NULL AS VARCHAR(6)) END;

    I think the rest of it might be done in a single MERGE statement, but I have no DDL.

    My real advice is that you ought to be using an audit tool. Microsoft has one and so do third parties.

  8. #8
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by blindman View Post
    There's a trigger just begging to be rewritten.
    Linksup, you need to dump all that conditional logic. Inner joins will perform the same effect, with more efficiency.
    OK, what part is begging the loudest?

    I changed the preamble to match Joe's suggestion. So assuming that part is OK. . .

    When you say dump the conditional logic are you referring to the code that does this:

    Code:
    if @act = 'UPDATE'
       Do something
    elsif @act = 'INSERT'
       DO something
    else
       DO Default
    end
    All this could be replaced with a MERGE as was suggested?

  9. #9
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by Celko View Post
    DDL would help. I think the rest of it might be done in a single MERGE statement, but I have no DDL.
    I don't know if you are seriously wanting to look at the DDL, but in case it is not just lamenting, here it is: (Inconsequential fields, columns, I meant columns, not fields, removed to keep the length within reason)

    Code:
    CREATE TABLE [dbo].[TRValue](
    	[RIN] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    	[Year] [int] NOT NULL,
    	[Parcel] [varchar](13) NOT NULL,
    	[PropCode] [varchar](4) NOT NULL,
    	[PropRIN] [int] NOT NULL,
    	[Sequence] [int] NOT NULL,
    	[SpcFlag] [bit] NOT NULL,
    	[Acres] [decimal](6, 2) NOT NULL,
    	[YearBuilt] [int] NOT NULL,
    	[CurrentMarket] [int] NOT NULL,
    	[CurrentTaxable] [int] NOT NULL,
    	[CurrentTaxAmt] [decimal](12, 2) NOT NULL,
    	[LastMarket] [int] NOT NULL,
    	[LastTaxable] [int] NOT NULL,
    	[LastTaxAmt] [decimal](12, 2) NOT NULL,
    	[ChangeType] [char](1) NOT NULL,
    	[MDateTime] [datetime] NOT NULL,
    	[MUser] [varchar](128) NOT NULL,
     CONSTRAINT [TRV_RINX] PRIMARY KEY CLUSTERED 
    (
    	[RIN] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF__TRValue__PropRIN__42ECDBF6]  DEFAULT ((0)) FOR [PropRIN]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF_TRValue_PropCode]  DEFAULT ('') FOR [Sequence]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF_TRValue_Sequence]  DEFAULT ((0)) FOR [Sequence]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF_TRValue_SpcFlag]  DEFAULT ((0)) FOR [SpcFlag]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF_TRValue_Acres]  DEFAULT ((0)) FOR [Acres]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF_TRValue_YearBuilt]  DEFAULT ((0)) FOR [YearBuilt]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF_TRValue_CurrentMarket]  DEFAULT ((0)) FOR [CurrentMarket]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF_TRValue_CurrentTaxable]  DEFAULT ((0)) FOR [CurrentTaxable]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF_TRValue_CurrentTaxAmt]  DEFAULT ((0)) FOR [CurrentTaxAmt]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF_TRValue_LastMarket]  DEFAULT ((0)) FOR [LastMarket]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF_TRValue_LastTaxable]  DEFAULT ((0)) FOR [LastTaxable]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF_TRValue_LastTaxAmt]  DEFAULT ((0)) FOR [LastTaxAmt]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF_TRValue_ChangeType]  DEFAULT ('N') FOR [ChangeType]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF__TRValue__MDateTi__43E1002F]  DEFAULT (getdate()) FOR [MDateTime]
    ALTER TABLE [dbo].[TRValue] ADD  CONSTRAINT [DF__TRValue__MUser__44D52468]  DEFAULT (suser_sname()) FOR [MUser]
    GO
    
    CREATE TABLE [dbo].[TRLog](
    	[RIN] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    	[Year] [int] NOT NULL,
    	[Parcel] [varchar](13) NOT NULL,
    	[ChangeType] [char](1) NOT NULL,
    	[OldCode] [varchar](4) NOT NULL,
    	[NewCode] [varchar](4) NOT NULL,
    	[OldSequence] [int] NOT NULL,
    	[NewSequence] [int] NOT NULL,
    	[OldAcres] [decimal](12, 2) NOT NULL,
    	[OldMarket] [int] NOT NULL,
    	[OldTaxable] [int] NOT NULL,
    	[NewAcres] [decimal](12, 2) NOT NULL,
    	[NewMarket] [int] NOT NULL,
    	[NewTaxable] [int] NOT NULL,
    	[LogDateTime] [datetime] NOT NULL,
    	[LogUser] [varchar](30) NOT NULL,
    	[District] [int] NOT NULL,
    	[MDateTime] [datetime] NOT NULL,
    	[MUser] [varchar](128) NOT NULL,
     CONSTRAINT [LOG_RINX] PRIMARY KEY CLUSTERED 
    (
    	[RIN] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_ChangeType]  DEFAULT ('N') FOR [ChangeType]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_OldCode]  DEFAULT ('') FOR [OldCode]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_NewCode]  DEFAULT ('') FOR [NewCode]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_OldSequence]  DEFAULT ((0)) FOR [OldSequence]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_NewSequence]  DEFAULT ((0)) FOR [NewSequence]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_OldAcres]  DEFAULT ((0)) FOR [OldAcres]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_OldMarket]  DEFAULT ((0)) FOR [OldMarket]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_OldTaxable]  DEFAULT ((0)) FOR [OldTaxable]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_NewAcres]  DEFAULT ((0)) FOR [NewAcres]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_NewMarket]  DEFAULT ((0)) FOR [NewMarket]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_NewTaxable]  DEFAULT ((0)) FOR [NewTaxable]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_LogDateTime]  DEFAULT (getdate()) FOR [LogDateTime]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_LogUser]  DEFAULT (suser_sname()) FOR [LogUser]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF_TRLog_District]  DEFAULT ((0)) FOR [District]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF__TRLog__MDateTime__396371BC]  DEFAULT (getdate()) FOR [MDateTime]
    ALTER TABLE [dbo].[TRLog] ADD  CONSTRAINT [DF__TRLog__MUser__3A5795F5]  DEFAULT (suser_sname()) FOR [MUser]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[TRMaster](
    	[RIN] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    	[Year] [int] NOT NULL,
    	[Parcel] [varchar](13) NOT NULL,
    	[Serial] [varchar](20) NOT NULL,
    	[NameRIN] [int] NOT NULL,
    	[Name] [varchar](50) NOT NULL,
    	[Name2] [varchar](50) NOT NULL,
    	[Add1] [varchar](50) NOT NULL,
    	[Add2] [varchar](50) NOT NULL,
    	[City] [varchar](30) NOT NULL,
    	[State] [varchar](2) NOT NULL,
    	[Zip] [int] NOT NULL,
    	[ZipDPoint] [int] NOT NULL,
    	[CRRT] [varchar](4) NOT NULL,
    	[District] [int] NOT NULL,
    	[MDateTime] [datetime] NOT NULL,
    	[MUser] [varchar](128) NOT NULL,
     CONSTRAINT [TRM_YearParcelNdx] PRIMARY KEY CLUSTERED 
    (
    	[Year] DESC,
    	[Parcel] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
     CONSTRAINT [TRM_ParcelYearNdx] UNIQUE NONCLUSTERED 
    (
    	[Parcel] ASC,
    	[Year] DESC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
     CONSTRAINT [TRM_RINX] UNIQUE NONCLUSTERED 
    (
    	[RIN] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__Year__6F7F8B4B]  DEFAULT ((0)) FOR [Year]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__Parcel__7073AF84]  DEFAULT ('') FOR [Parcel]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__Serial__7167D3BD]  DEFAULT ('') FOR [Serial]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__NameRI__725BF7F6]  DEFAULT ((0)) FOR [NameRIN]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__Name__73501C2F]  DEFAULT ('') FOR [Name]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__Name2__74444068]  DEFAULT ('') FOR [Name2]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__Add1__753864A1]  DEFAULT ('') FOR [Add1]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__Add2__762C88DA]  DEFAULT ('') FOR [Add2]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__City__7720AD13]  DEFAULT ('') FOR [City]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__State__7814D14C]  DEFAULT ('UT') FOR [State]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__Zip__7908F585]  DEFAULT ((0)) FOR [Zip]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__ZipDPo__79FD19BE]  DEFAULT ((0)) FOR [ZipDPoint]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__CRRT__7AF13DF7]  DEFAULT ('') FOR [CRRT]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__Distri__7BE56230]  DEFAULT ((0)) FOR [District]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__MDateT__14B10FFA]  DEFAULT (getdate()) FOR [MDateTime]
    ALTER TABLE [dbo].[TRMaster] ADD  CONSTRAINT [DF__TRMaster__MUser__15A53433]  DEFAULT (suser_sname()) FOR [MUser]
    GO
    Last edited by LinksUp; 03-26-13 at 16:08. Reason: Used the word fields instead of columns

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    A minor improvement you can make is to switch out the call to sys.sysprocesses with calls to the user_name() or suser_sname() functions as needed. sys.sysprocesses is only there for backwards compatibility to SQL 2000, and could be deprecated/removed soon.

  11. #11
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by MCrowley View Post
    A minor improvement you can make is to switch out the call to sys.sysprocesses with calls to the user_name() or suser_sname() functions as needed. sys.sysprocesses is only there for backwards compatibility to SQL 2000, and could be deprecated/removed soon.
    I had done that until I found that for servers that use Windows Authentication, suser_sname returns the Domain and the user name. I really did not want or need the domain name. If you know a better way, I am all for it!!

  12. #12
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Thanks for the DDL

    Here are two audit tools you could buy. Google for more of them.

    eyeLog - Database Audit Tool for Microsoft SQL Server - Home

    ChangeAuditor for SQL Server

    I would throw out what you have and start over. What you have has no ISO-11179 names, uses assembly language Bit flags, improper data types, IDENTITY instead of valid keys and no DRI. This is a set of mag tape files badly written in T-SQL.

    For example, the mailing address subset was done without any USPS standards. Labels are 35 characters wide to fit into 3.5 inch labels using 10-pitch type. Why do you think that “name” is precise and clear? I find it generic and vague.

    Why is a state_code varying length? ZIP codes are not integers. The VARCHAR(50) is a lazy ACCESS programmer using defaults instead of programming. Have you ever heard of the CASS standards? The nine-one-one address conventions? There are no more second lines.

    The schema should use a skeleton like this:

    customer_name VARCHAR(35) NOT NULL,
    street_address VARCHAR(35) NOT NULL,
    city_name VARCHAR(25) NOT NULL,
    state_code CHAR(2) NOT NULL
    CHECK (state_code IN (..)),
    zip_code CHAR(5) NOT NULL
    CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]'),

    The rest of the DDL is just as bad. My guess is that if you really want to do this, then the skeleton might be something like this, after the schema is brought up to standards.

    MERGE INTO Log_Table
    USING (<table expression using INSERTED and DELETED>) AS Source
    ON Source.real_key = Log_Table.real_key
    WHEN MATCHED
    THEN UPDATE …
    WHEN NOT MATCHED
    THEN INSERT …;

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Do you need this?
    Code:
    select substring (suser_sname(), charindex ('\', suser_sname()) + 1, 100)

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The non-ISO names are your decision. There is no law that says you need to follow them.
    The bit datatypes are likewise acceptable.
    The IDENTITY columns are simple, efficient, and consistent, though they are not the best choice for a primary key, and they are no substitute for having a natural key defined and enforced (which you appear to be missing).
    You don't need to throw out your whole system and buy something off the shelf.

    The problem is with the conditional logic and variables required to determine and store whether a given transaction is an update, insert, or delete.

    An inner join to "inserted" and "deleted" gives you the updates.
    An inner join to "inserted" with a left outer join to "deleted where key is null" gives you the inserts.

    An inner join to "deleted" with a left outer join to "inserted where key is null" gives you the deletes.

    A "MERGE" may or may not get you want you want. Probably not, in this case.

    Checking for the existence of records that will be affected by these statements is unnecessary. If there are not inserts, the insert logic won't affect any records, etc.

    Your code will be clearer (especially if you add some comments, hint hint...), and faster, without the variables and conditional logic.
    Last edited by blindman; 03-26-13 at 17:36.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by blindman View Post
    The non-ISO names are your decision. There is no law that says you need to follow them.
    The bit datatypes are likewise acceptable.
    The IDENTITY columns are simple, efficient, and consistent, though they are not the best choice for a primary key, and they are no substitute for having a natural key defined and enforced (which you appear to be missing).
    If you look at the DDL for the TRMaster table, I do use NK's. The other tables do have additional indexes but were left off because they were not relevant to the question, which was triggers!

    Quote Originally Posted by blindman View Post
    The problem is with the conditional logic and variables required to determine and store whether a given transaction is an update, insert, or delete.

    An inner join to "inserted" and "deleted" gives you the updates.
    An inner join to "inserted" with a left outer join to "deleted where key is null" gives you the inserts.

    An inner join to "deleted" with a left outer join to "inserted where key is null" gives you the deletes.

    A "MERGE" may or may not get you want you want. Probably not, in this case.

    Your code will be clearer (especially if you add some comments, hint hint...), and faster, without the variables and conditional logic.
    Thank you for the info and direction. I will be spending some time working re-working this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •