How exactly an access violation exception is triggered

Memory access violations are a large topic :)

The Protection of Information in Computer Systems (from 1973 :) lays out of a mechanism of segments, where processes are allocated a base and a bound; any attempt to access memory outside the range base:base+bound meant the program had done something silly and should be killed.

The 80x86 line of processors implement basic segment support, and the GEMSOS security kernel is an A1-certified operating system kernel based on this mechanism.

But segments aren't very dynamic, and almost all modern operating systems are paging systems, that page in memory when it isn't available. This relies on the CPU having an MMU, memory management unit, that checks all memory accesses for correct privileges and presence/absence of the correct memory mapping. When a process tries to access memory that isn't currently mapped into RAM, the MMU signals the CPU that a fault has occurred, and the CPU suspends the process to load the requested memory page from disk. (Or, if the memory should not be mapped for the process, say it tries to access 0x0 or some random memory location that hasn't been mapped with mmap or similar memory allocating primitives, it kills the process.)

Intel's 80386 was the first Intel chip to support paging, which is why Windows 3.1's "386 Enchanced Mode" was so much better than the 286 mode.

Compilers aren't really involved, but the CPU, MMU, and operating system kernel must all work together.

In x86 architecture (and most others as well), this is started from the MMU - the Memory Management Unit. The MMU is used to translate virtual memory addresses to physical memory addresses. If a request is made to access an invalid address (0x00000000 or something too high) the MMU will trap (interrupt) to the OS (this is in fact done for every access not in the TLB (Translate Lookaside Buffer - the MMU translation "cache")). Here the OS will be able to tell that this is an illegal memory access, and propagate to the user application via the OS-dependent mechanism (signals in Linux (SIGSEGV), I'm not familiar with Windows enough to say how it is done in it).

This feature is available for any modern CPU, OS and compiler. The most basic requirement is an MMU, which is present in all but the most basic embedded CPUs. I doubt there is any PC currently operating that doesn't support this.

Edit:

Following the OP edit, when a literal string is used, the memory is placed in the .text segment of the executable. This is where the binary code and constant values sit. Naturally, in most OSs this is read-only (especially under Linux with various security enhancements). When you try to change a value of a literal string you are basically trying to write to a read-only memory, causing an access violation. Again, this is caught by the MMU that sees a write command to a read-only memory address.

When you try to access a memory address, the computer goes through several steps:

• If the address is part of the current memory segment, access is granted.
• Otherwise, if the address's segment is in memory with appropriate access permission, access is granted.

If the address is not in memory, the CPU will generate a memory check exception. At this point, the operating system takes over.

• If the segment is available in virtual memory with appropriate access permission, it is loaded into memory and assigned to the virtual memory manager; access is then granted.

If, at this point, the memory is not available, there is one of two possibilities. Either the address is not available, or you do not have the permissions you need (eg, trying to write into read-only memory). In this case, the operating system will pass the access violation to the process.

As to CPU and OS versions, this is any system that allows virtual memory. I don't know the details of this.